Paytm: Scratch Cards That Expired Unscratched

Medium
860.

Paytm: Scratch Cards That Expired Unscratched

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

ColumnType
card_idint
user_idint
issued_ondate
expires_ondate
scratched_ondate
rewardint

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.

Example

The rows must come back in the order shown.

ScratchCard

card_iduser_idissued_onexpires_onscratched_onreward
112025-03-012025-03-15NULL25
212025-03-052025-03-192025-03-0610
312025-03-202025-04-03NULL50
422025-02-202025-03-31NULL40
522025-03-012025-04-01NULL15
632025-03-022025-03-162025-03-1630
742025-02-102025-02-24NULL5
842025-02-122025-02-26NULL20

Output

user_idcards_receivedcards_wastedvalue_wasted
22140
13125
42225
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: 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

ColumnType
card_idint
user_idint
issued_ondate
expires_ondate
scratched_ondate
rewardint

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_iduser_idissued_onexpires_onscratched_onreward
112025-03-012025-03-15NULL25
212025-03-052025-03-192025-03-0610
312025-03-202025-04-03NULL50
422025-02-202025-03-31NULL40
522025-03-012025-04-01NULL15
632025-03-022025-03-162025-03-1630
742025-02-102025-02-24NULL5
842025-02-122025-02-26NULL20

Output

user_idcards_receivedcards_wastedvalue_wasted
22140
13125
42225

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_id

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