Flipkart: Plus Members Sitting on Unused SuperCoins
The Flipkart Plus loyalty team wants to nudge members who have piled up SuperCoins but have not spent any of them this year.
- Difficulty: Easy
- Topics: Basics, Conditional Logic
- Dialect: MySQL
- Problem: #648
Problem statement
The Flipkart Plus loyalty team wants to nudge members who have piled up SuperCoins but have not spent any of them this year.
Return every member whose tier is Plus or Plus Premium (not Lapsed), whose supercoin_balance is at least 500, and who has not redeemed since 2025-01-01 — either last_redeemed_on is NULL (never redeemed) or it is before 2025-01-01. Return member_id, member_name and supercoin_balance, ordered by supercoin_balance descending, then member_id ascending.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: PlusMember
| Column | Type |
|---|---|
| member_id | int |
| member_name | varchar |
| tier | enum(Plus, Plus Premium, Lapsed) |
| supercoin_balance | int |
| last_redeemed_on | date |
Primary key: member_id.
One row per loyalty member. last_redeemed_on is the date of the member's latest SuperCoin redemption, or NULL if they have never redeemed.
Examples
Example 1
PlusMember
| member_id | member_name | tier | supercoin_balance | last_redeemed_on |
|---|---|---|---|---|
| 1 | Aarav | Plus | 820 | NULL |
| 2 | Diya | Plus Premium | 500 | 2024-12-31 |
| 3 | Kabir | Lapsed | 1500 | NULL |
| 4 | Meera | Plus | 1200 | 2025-01-01 |
| 5 | Rohan | Plus | 499 | NULL |
| 6 | Zara | Plus Premium | 820 | 2024-06-10 |
Output
| member_id | member_name | supercoin_balance |
|---|---|---|
| 1 | Aarav | 820 |
| 6 | Zara | 820 |
| 2 | Diya | 500 |
How to solve Flipkart: Plus Members Sitting on Unused SuperCoins
Every member either qualifies or not on their own row, so this is a filter and a sort. The tier test keeps Plus and Plus Premium and drops Lapsed; the balance test is >= 500 because a member with exactly 500 coins counts.
The redemption test is where the NULLs bite. A member who has never redeemed has last_redeemed_on NULL, and NULL < '2025-01-01' is not true, so a plain comparison would silently drop the very members the team most wants to reach. Write it as last_redeemed_on IS NULL OR last_redeemed_on < '2025-01-01' — and keep the parentheses, because AND binds tighter than OR. A member who redeemed on 2025-01-01 itself has redeemed this year and is left out.
The alternative turns the NULL into an impossibly old date with COALESCE, which reads the same. Ties on balance are broken by member_id so the order is fixed. It is one scan of the table.
Reference solution (MySQL)
SELECT member_id, member_name, supercoin_balance
FROM PlusMember
WHERE tier IN ('Plus', 'Plus Premium')
AND supercoin_balance >= 500
AND (last_redeemed_on IS NULL OR last_redeemed_on < '2025-01-01')
ORDER BY supercoin_balance DESC, member_idAnother way
SELECT member_id, member_name, supercoin_balance FROM PlusMember
WHERE tier <> 'Lapsed' AND NOT supercoin_balance < 500
AND COALESCE(last_redeemed_on, '1900-01-01') < '2025-01-01'
ORDER BY supercoin_balance DESC, member_id ASC← Dream11: Biggest Live Leaderboard Climb of Each Team · Flipkart: COD Orders Shipped to Pincodes Without COD →