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
| Column | Type |
|---|---|
| refund_id | int |
| payment_id | int |
| speed | enum(normal, instant) |
| amount | int |
| created_on | date |
| processed_on | date |
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_id | payment_id | speed | amount | created_on | processed_on |
|---|---|---|---|---|---|
| 1 | 5001 | normal | 499 | 2025-03-01 | 2025-03-06 |
| 2 | 5002 | normal | 1200 | 2025-03-01 | 2025-03-08 |
| 3 | 5003 | normal | 300 | 2025-03-02 | 2025-03-12 |
| 4 | 5004 | instant | 800 | 2025-03-02 | 2025-03-11 |
| 5 | 5005 | normal | 2500 | 2025-03-03 | NULL |
| 6 | 5006 | normal | 150 | 2025-03-04 | 2025-03-12 |
| 7 | 5007 | normal | 999 | 2025-03-05 | 2025-03-15 |
Output
| refund_id | payment_id | days_taken |
|---|---|---|
| 3 | 5003 | 10 |
| 7 | 5007 | 10 |
| 6 | 5006 | 8 |
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_idAnother 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 →