Paytm: Failed Recharges Still Awaiting a Refund

Easy
848.

Paytm: Failed Recharges Still Awaiting a Refund

EasyBasics

MySQL

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

ColumnTypeValues
recharge_idint
user_idint
operatorvarchar
recharge_typeenummobile, dth
amountint
statusenumsuccess, failed, pending
refundedbool
created_atdatetime

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.

Example

The rows must come back in the order shown.

Recharge

recharge_iduser_idoperatorrecharge_typeamountstatusrefundedcreated_at
111Jiomobile299failed02025-03-06 21:14:05
212Tata Playdth450failed12025-03-05 09:30:00
313Airtelmobile719pending02025-03-04 18:02:44
411Vimobile155failed02025-03-08 00:00:00
514Dish TVdth350failed02025-03-02 07:45:10
615Jiomobile239success02025-03-03 12:00:00
716BSNLmobile107failed02025-03-07 23:59:59

Output

recharge_iduser_idoperatoramount
514Dish TV350
111Jio299
716BSNL107
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.

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

ColumnType
recharge_idint
user_idint
operatorvarchar
recharge_typeenum(mobile, dth)
amountint
statusenum(success, failed, pending)
refundedbool
created_atdatetime

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_iduser_idoperatorrecharge_typeamountstatusrefundedcreated_at
111Jiomobile299failed02025-03-06 21:14:05
212Tata Playdth450failed12025-03-05 09:30:00
313Airtelmobile719pending02025-03-04 18:02:44
411Vimobile155failed02025-03-08 00:00:00
514Dish TVdth350failed02025-03-02 07:45:10
615Jiomobile239success02025-03-03 12:00:00
716BSNLmobile107failed02025-03-07 23:59:59

Output

recharge_iduser_idoperatoramount
514Dish TV350
111Jio299
716BSNL107

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_id

Another 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 →