PhonePe: Labelling Scratch Card Rewards for the Rewards Page
After a payment, users may get a scratch card. The rewards page shows one label per card, and the app team wants the labels computed in SQL so every client…
- Difficulty: Easy
- Topics: Conditional Logic
- Dialect: MySQL
- Problem: #880
Problem statement
After a payment, users may get a scratch card. The rewards page shows one label per card, and the app team wants the labels computed in SQL so every client shows the same words.
Return card_id, user_id and label, where the label is: No reward when reward_type is none or it is a cashback worth 0; Jackpot for a cashback of 100 or more; Cashback for any other cashback; and Voucher for a voucher. cashback_amount is NULL exactly when the card is not a cashback. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: ScratchCard
| Column | Type |
|---|---|
| card_id | int |
| user_id | int |
| reward_type | enum(cashback, voucher, none) |
| cashback_amount | int |
| issued_on | date |
Primary key: card_id.
One row per scratch card; cashback_amount is in rupees and NULL for vouchers and empty cards.
Examples
Example 1
ScratchCard
| card_id | user_id | reward_type | cashback_amount | issued_on |
|---|---|---|---|---|
| 1 | 31 | cashback | 12 | 2025-04-01 |
| 2 | 31 | none | NULL | 2025-04-01 |
| 3 | 32 | cashback | 100 | 2025-04-02 |
| 4 | 33 | voucher | NULL | 2025-04-02 |
| 5 | 33 | cashback | 0 | 2025-04-03 |
| 6 | 34 | cashback | 99 | 2025-04-04 |
| 7 | 35 | cashback | 250 | 2025-04-05 |
Output
| card_id | user_id | label |
|---|---|---|
| 1 | 31 | Cashback |
| 2 | 31 | No reward |
| 3 | 32 | Jackpot |
| 4 | 33 | Voucher |
| 5 | 33 | No reward |
| 6 | 34 | Cashback |
| 7 | 35 | Jackpot |
How to solve PhonePe: Labelling Scratch Card Rewards for the Rewards Page
A searched CASE returns the result of the first branch whose condition is true, so the order of the branches is the specification. The empty outcomes come first: an empty card, or a cashback card worth 0, is No reward. Vouchers are next. What is left is a cashback with a positive amount, split at the boundary >= 100 into Jackpot and Cashback — 100 itself is a jackpot, 99 is not.
NULL needs a thought. For vouchers and empty cards cashback_amount is NULL, and NULL >= 100 is unknown, not true — so a voucher must be caught by its own branch before the amount tests, or it would fall through to ELSE 'Cashback'. Listing the branches as above avoids that.
The nested IF alternative folds the two empty cases into IFNULL(cashback_amount, 0) = 0 after vouchers are handled. The UNION ALL alternative builds each label from its own filter; it works because the four filters are disjoint and cover every row. All are a single pass.
Reference solution (MySQL)
SELECT card_id, user_id,
CASE
WHEN reward_type = 'none' OR (reward_type = 'cashback' AND cashback_amount = 0) THEN 'No reward'
WHEN reward_type = 'voucher' THEN 'Voucher'
WHEN cashback_amount >= 100 THEN 'Jackpot'
ELSE 'Cashback'
END AS label
FROM ScratchCardAnother way
SELECT card_id, user_id, IF(reward_type = 'voucher', 'Voucher', IF(IFNULL(cashback_amount, 0) = 0, 'No reward', IF(cashback_amount >= 100, 'Jackpot', 'Cashback'))) AS label FROM ScratchCardAnother way
SELECT card_id, user_id, 'Voucher' AS label FROM ScratchCard WHERE reward_type = 'voucher' UNION ALL SELECT card_id, user_id, 'No reward' FROM ScratchCard WHERE reward_type = 'none' OR cashback_amount = 0 UNION ALL SELECT card_id, user_id, 'Jackpot' FROM ScratchCard WHERE cashback_amount >= 100 UNION ALL SELECT card_id, user_id, 'Cashback' FROM ScratchCard WHERE cashback_amount BETWEEN 1 AND 99← PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included · PhonePe: Most Frequent UPI Decline Code at Each Remitter Bank →