Flipkart: Plus Members Sitting on Unused SuperCoins

Easy
648.

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.

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

ColumnTypeValues
member_idint
member_namevarchar
tierenumPlus, Plus Premium, Lapsed
supercoin_balanceint
last_redeemed_ondate

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.

Example

The rows must come back in the order shown.

PlusMember

member_idmember_nametiersupercoin_balancelast_redeemed_on
1AaravPlus820NULL
2DiyaPlus Premium5002024-12-31
3KabirLapsed1500NULL
4MeeraPlus12002025-01-01
5RohanPlus499NULL
6ZaraPlus Premium8202024-06-10

Output

member_idmember_namesupercoin_balance
1Aarav820
6Zara820
2Diya500
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.

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

ColumnType
member_idint
member_namevarchar
tierenum(Plus, Plus Premium, Lapsed)
supercoin_balanceint
last_redeemed_ondate

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_idmember_nametiersupercoin_balancelast_redeemed_on
1AaravPlus820NULL
2DiyaPlus Premium5002024-12-31
3KabirLapsed1500NULL
4MeeraPlus12002025-01-01
5RohanPlus499NULL
6ZaraPlus Premium8202024-06-10

Output

member_idmember_namesupercoin_balance
1Aarav820
6Zara820
2Diya500

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_id

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