Paytm: Failed Recharges Still Awaiting a Refund
The recharges operations desk promises that money for a failed mobile or DTH recharge goes back to the customer within 48 hours.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #848
Problem statement
The recharges operations desk promises that money for a failed mobile or DTH recharge goes back to the customer within 48 hours. Today is 2025-03-10, so any failure created before 2025-03-08 00:00:00 that is still not refunded has broken the promise.
Return every recharge whose status is failed, whose refunded flag is 0, and whose created_at is strictly before 2025-03-08 00:00:00. Pending and successful recharges never qualify. Return the columns recharge_id, user_id, operator and amount, ordered by created_at, then by recharge_id.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: Recharge
| Column | Type |
|---|---|
| recharge_id | int |
| user_id | int |
| operator | varchar |
| recharge_type | enum(mobile, dth) |
| amount | int |
| status | enum(success, failed, pending) |
| refunded | bool |
| created_at | datetime |
Primary key: recharge_id.
One row per recharge attempt. refunded is 1 once the amount has gone back to the customer's wallet or bank.
Examples
Example 1
Recharge
| recharge_id | user_id | operator | recharge_type | amount | status | refunded | created_at |
|---|---|---|---|---|---|---|---|
| 1 | 11 | Jio | mobile | 299 | failed | 0 | 2025-03-06 21:14:05 |
| 2 | 12 | Tata Play | dth | 450 | failed | 1 | 2025-03-05 09:30:00 |
| 3 | 13 | Airtel | mobile | 719 | pending | 0 | 2025-03-04 18:02:44 |
| 4 | 11 | Vi | mobile | 155 | failed | 0 | 2025-03-08 00:00:00 |
| 5 | 14 | Dish TV | dth | 350 | failed | 0 | 2025-03-02 07:45:10 |
| 6 | 15 | Jio | mobile | 239 | success | 0 | 2025-03-03 12:00:00 |
| 7 | 16 | BSNL | mobile | 107 | failed | 0 | 2025-03-07 23:59:59 |
Output
| recharge_id | user_id | operator | amount |
|---|---|---|---|
| 5 | 14 | Dish TV | 350 |
| 1 | 11 | Jio | 299 |
| 7 | 16 | BSNL | 107 |
How to solve Paytm: Failed Recharges Still Awaiting a Refund
Each recharge either breaks the refund promise or it does not, so this is a filter on one table. The three conditions are joined with AND: the attempt failed (status = 'failed' — a pending recharge may still go through, so it is not owed anything yet), no refund has been recorded (refunded = 0), and it was created before the cut-off.
The cut-off is strict: a recharge created at exactly 2025-03-08 00:00:00 is 48 hours old only at the end of today, so < and not <= is right. Datetimes stored as YYYY-MM-DD HH:MM:SS compare correctly as text, which is why the alternative can write the same bound as <= '2025-03-07 23:59:59' — the timestamps carry whole seconds, so both forms keep the same rows.
The order is fixed by the statement, so the query ends with ORDER BY created_at, recharge_id; the id is the tie-breaker for two failures in the same second. An index on (status, created_at) lets the database read only failed attempts in the window.
Reference solution (MySQL)
SELECT recharge_id, user_id, operator, amount
FROM Recharge
WHERE status = 'failed'
AND refunded = 0
AND created_at < '2025-03-08 00:00:00'
ORDER BY created_at, recharge_idAnother way
SELECT recharge_id, user_id, operator, amount FROM Recharge WHERE status IN ('failed') AND NOT refunded AND created_at <= '2025-03-07 23:59:59' ORDER BY created_at, recharge_id← Ola: Service Tickets Breaching the 3-Working-Day SLA · Paytm: Soundbox Devices That Never Announced a Payment →