PhonePe: Labelling Scratch Card Rewards for the Rewards Page

Easy
880.

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

ColumnTypeValues
card_idint
user_idint
reward_typeenumcashback, voucher, none
cashback_amountint
issued_ondate

Primary key: card_id.

One row per scratch card; cashback_amount is in rupees and NULL for vouchers and empty cards.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

ScratchCard

card_iduser_idreward_typecashback_amountissued_on
131cashback122025-04-01
231noneNULL2025-04-01
332cashback1002025-04-02
433voucherNULL2025-04-02
533cashback02025-04-03
634cashback992025-04-04
735cashback2502025-04-05

Output

card_iduser_idlabel
131Cashback
231No reward
332Jackpot
433Voucher
533No reward
634Cashback
735Jackpot
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.

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

ColumnType
card_idint
user_idint
reward_typeenum(cashback, voucher, none)
cashback_amountint
issued_ondate

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_iduser_idreward_typecashback_amountissued_on
131cashback122025-04-01
231noneNULL2025-04-01
332cashback1002025-04-02
433voucherNULL2025-04-02
533cashback02025-04-03
634cashback992025-04-04
735cashback2502025-04-05

Output

card_iduser_idlabel
131Cashback
231No reward
332Jackpot
433Voucher
533No reward
634Cashback
735Jackpot

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 ScratchCard

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

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