Razorpay: Normal-Speed Refunds That Took Over a Week

Easy
903.

Razorpay: Normal-Speed Refunds That Took Over a Week

EasyDatesBasics

MySQL

Razorpay's customer support team promises that a normal-speed refund reaches the customer within a week. They want the refunds that broke that promise, slowest first.

Return every refund with speed = 'normal' that has been processed and whose days_taken — the days from created_on to processed_on — is more than 7. Return refund_id, payment_id and days_taken, ordered by days_taken descending, then refund_id ascending. Instant refunds and refunds still pending (processed_on NULL) are not reported.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: Refund

ColumnTypeValues
refund_idint
payment_idint
speedenumnormal, instant
amountint
created_ondate
processed_ondate

Primary key: refund_id.

One row per refund a merchant raised; processed_on is NULL while the bank has not confirmed it.

Example

The rows must come back in the order shown.

Refund

refund_idpayment_idspeedamountcreated_onprocessed_on
15001normal4992025-03-012025-03-06
25002normal12002025-03-012025-03-08
35003normal3002025-03-022025-03-12
45004instant8002025-03-022025-03-11
55005normal25002025-03-03NULL
65006normal1502025-03-042025-03-12
75007normal9992025-03-052025-03-15

Output

refund_idpayment_iddays_taken
3500310
7500710
650068
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Razorpay: Normal-Speed Refunds That Took Over a Week

Razorpay's customer support team promises that a normal-speed refund reaches the customer within a week.

  • Difficulty: Easy
  • Topics: Dates, Basics
  • Dialect: MySQL
  • Problem: #903

Problem statement

Razorpay's customer support team promises that a normal-speed refund reaches the customer within a week. They want the refunds that broke that promise, slowest first.

Return every refund with speed = 'normal' that has been processed and whose days_taken — the days from created_on to processed_on — is more than 7. Return refund_id, payment_id and days_taken, ordered by days_taken descending, then refund_id ascending. Instant refunds and refunds still pending (processed_on NULL) are not reported.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: Refund

ColumnType
refund_idint
payment_idint
speedenum(normal, instant)
amountint
created_ondate
processed_ondate

Primary key: refund_id.

One row per refund a merchant raised; processed_on is NULL while the bank has not confirmed it.

Examples

Example 1

Refund

refund_idpayment_idspeedamountcreated_onprocessed_on
15001normal4992025-03-012025-03-06
25002normal12002025-03-012025-03-08
35003normal3002025-03-022025-03-12
45004instant8002025-03-022025-03-11
55005normal25002025-03-03NULL
65006normal1502025-03-042025-03-12
75007normal9992025-03-052025-03-15

Output

refund_idpayment_iddays_taken
3500310
7500710
650068

How to solve Razorpay: Normal-Speed Refunds That Took Over a Week

The days a refund took are DATEDIFF(processed_on, created_on), the later date first. The filter keeps normal refunds whose difference is strictly greater than 7: a refund done in exactly seven days kept the promise.

Pending refunds have processed_on NULL, so DATEDIFF returns NULL and the comparison is unknown; WHERE drops them even without the explicit IS NOT NULL, which is there for the reader. Instant refunds are excluded by the speed test.

The order is part of the answer: days_taken DESC puts the slowest refunds first, and refund_id breaks ties so the result is fully determined. The alternative compares dates directly — processed_on > DATE_ADD(created_on, INTERVAL 7 DAY) — which lets an index on the dates be used, and computes the difference with TIMESTAMPDIFF(DAY, …). Both are a single scan plus a sort of the matching rows.

Reference solution (MySQL)

SELECT refund_id, payment_id, DATEDIFF(processed_on, created_on) AS days_taken
FROM Refund
WHERE speed = 'normal'
  AND processed_on IS NOT NULL
  AND DATEDIFF(processed_on, created_on) > 7
ORDER BY days_taken DESC, refund_id

Another way

SELECT refund_id, payment_id, TIMESTAMPDIFF(DAY, created_on, processed_on) AS days_taken
FROM Refund
WHERE speed = 'normal' AND processed_on > DATE_ADD(created_on, INTERVAL 7 DAY)
ORDER BY 3 DESC, 1

← Razorpay: Gateway Fee and Settled Amount for Each Payment · Razorpay: Fund Accounts With a Malformed UPI ID →