Paytm: Scratch Cards That Expired Unscratched — SQL Problem
Cashback scratch cards must be scratched before they expire. The rewards team is studying users who let rewards lapse. The report date is 2025-04-01.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #860
Problem statement
Cashback scratch cards must be scratched before they expire. The rewards team is studying users who let rewards lapse. The report date is 2025-04-01.
A card is wasted when it was never scratched (scratched_on is NULL) and its expires_on is before 2025-04-01 — a card expiring on the report date is still valid. For every user with at least one wasted card, return user_id, cards_received (all their cards), cards_wasted and value_wasted (the rewards of the wasted cards). Return the rows ordered by value_wasted descending, then by user_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: ScratchCard
| Column | Type |
|---|---|
| card_id | int |
| user_id | int |
| issued_on | date |
| expires_on | date |
| scratched_on | date |
| reward | int |
Primary key: card_id.
One row per scratch card issued after a payment. scratched_on is NULL until the user scratches it; reward is the cashback in rupees hidden under it.
Examples
Example 1
ScratchCard
| card_id | user_id | issued_on | expires_on | scratched_on | reward |
|---|---|---|---|---|---|
| 1 | 1 | 2025-03-01 | 2025-03-15 | NULL | 25 |
| 2 | 1 | 2025-03-05 | 2025-03-19 | 2025-03-06 | 10 |
| 3 | 1 | 2025-03-20 | 2025-04-03 | NULL | 50 |
| 4 | 2 | 2025-02-20 | 2025-03-31 | NULL | 40 |
| 5 | 2 | 2025-03-01 | 2025-04-01 | NULL | 15 |
| 6 | 3 | 2025-03-02 | 2025-03-16 | 2025-03-16 | 30 |
| 7 | 4 | 2025-02-10 | 2025-02-24 | NULL | 5 |
| 8 | 4 | 2025-02-12 | 2025-02-26 | NULL | 20 |
Output
| user_id | cards_received | cards_wasted | value_wasted |
|---|---|---|---|
| 2 | 2 | 1 | 40 |
| 1 | 3 | 1 | 25 |
| 4 | 2 | 2 | 25 |
How to solve Paytm: Scratch Cards That Expired Unscratched
The report needs, per user, a count over all their cards and two figures over only the wasted ones, so a plain WHERE filter would lose cards_received. Conditional aggregation solves it in one grouping: COUNT(*) counts every card, while SUM(CASE WHEN <wasted> THEN 1 ELSE 0 END) and SUM(CASE WHEN <wasted> THEN reward ELSE 0 END) count and value only the wasted ones.
The wasted test has two halves. scratched_on IS NULL — not = NULL, which is never true — means the reward was never claimed; expires_on < '2025-04-01' means it is already gone. A card expiring on the report day itself is still usable, so the comparison is strict, and an unscratched card that has not expired yet is merely pending. HAVING cards_wasted > 0 then drops users who wasted nothing.
The alternative computes the two halves in separate derived tables — all cards per user, and wasted cards per user — and joins them; the inner join drops users with no wasted card. One scan for the first query, two for the second.
Reference solution (MySQL)
SELECT user_id,
COUNT(*) AS cards_received,
SUM(CASE WHEN scratched_on IS NULL AND expires_on < '2025-04-01' THEN 1 ELSE 0 END) AS cards_wasted,
SUM(CASE WHEN scratched_on IS NULL AND expires_on < '2025-04-01' THEN reward ELSE 0 END) AS value_wasted
FROM ScratchCard
GROUP BY user_id
HAVING cards_wasted > 0
ORDER BY value_wasted DESC, user_idAnother way
SELECT a.user_id, a.cards_received, w.cards_wasted, w.value_wasted
FROM (SELECT user_id, COUNT(*) AS cards_received FROM ScratchCard GROUP BY user_id) a
JOIN (SELECT user_id, COUNT(*) AS cards_wasted, SUM(reward) AS value_wasted FROM ScratchCard
WHERE scratched_on IS NULL AND DATEDIFF('2025-04-01', expires_on) > 0 GROUP BY user_id) w ON w.user_id = a.user_id
ORDER BY w.value_wasted DESC, a.user_id← Paytm Money: SIPs That Bounced Twice in a Row · Paytm: Busiest Day for Each EDC Card Machine →