Flipkart: Months Members Burned More SuperCoins Than Earned
The SuperCoin ledger records every coin a member earns on a purchase, burns on a reward, or loses to expiry.
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #657
Problem statement
The SuperCoin ledger records every coin a member earns on a purchase, burns on a reward, or loses to expiry. The loyalty analysts want the member-months where a member spent down their balance.
For each member and calendar month with ledger activity, earned is the total coins of earn entries and burned the total of burn entries (expire entries count as neither). Return member_id, ledger_month ('YYYY-MM'), earned and burned for every member-month where burned is strictly greater than earned — a month with burns and no earns has earned 0. Order by member_id, then ledger_month.
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: SuperCoinLedger
| Column | Type |
|---|---|
| entry_id | int |
| member_id | int |
| entry_at | datetime |
| coins | int |
| entry_type | enum(earn, burn, expire) |
Primary key: entry_id.
One row per ledger entry; coins is always positive — the type says which way it moved.
Examples
Example 1
SuperCoinLedger
| entry_id | member_id | entry_at | coins | entry_type |
|---|---|---|---|---|
| 1 | 7 | 2024-07-03 10:15:00 | 120 | earn |
| 2 | 7 | 2024-07-21 19:02:11 | 200 | burn |
| 3 | 7 | 2024-08-02 08:00:00 | 50 | earn |
| 4 | 7 | 2024-08-30 22:41:09 | 50 | burn |
| 5 | 9 | 2024-07-11 13:30:00 | 300 | expire |
| 6 | 9 | 2024-07-12 09:05:44 | 40 | burn |
| 7 | 9 | 2024-08-01 00:00:00 | 500 | earn |
Output
| member_id | ledger_month | earned | burned |
|---|---|---|---|
| 7 | 2024-07 | 120 | 200 |
| 9 | 2024-07 | 0 | 40 |
How to solve Flipkart: Months Members Burned More SuperCoins Than Earned
Both totals come from the same rows, so instead of two queries the solution uses conditional aggregation: SUM(CASE WHEN entry_type = 'earn' THEN coins ELSE 0 END) adds only the earn entries of a group and contributes 0 for the rest, and a second SUM does the same for burns. Expiry entries fall into neither branch, as the statement asks. The ELSE 0 matters: a month with burns but no earns gets earned = 0, not NULL, so the comparison still works.
The group is the member and the month, LEFT(entry_at, 7). Grouping by the very expression that is selected satisfies MySQL's ONLY_FULL_GROUP_BY. The filter compares two aggregates, so it runs after grouping — on a derived table here, or as a HAVING in the alternative, which formats the month with DATE_FORMAT and uses IF in place of CASE.
Equal totals are not "more" and are left out. It is one scan of the ledger plus the grouping.
Reference solution (MySQL)
SELECT member_id, ledger_month, earned, burned
FROM (
SELECT member_id, LEFT(entry_at, 7) AS ledger_month,
SUM(CASE WHEN entry_type = 'earn' THEN coins ELSE 0 END) AS earned,
SUM(CASE WHEN entry_type = 'burn' THEN coins ELSE 0 END) AS burned
FROM SuperCoinLedger
GROUP BY member_id, LEFT(entry_at, 7)
) m
WHERE burned > earned
ORDER BY member_id, ledger_monthAnother way
SELECT member_id, DATE_FORMAT(entry_at, '%Y-%m') AS ledger_month,
SUM(IF(entry_type = 'earn', coins, 0)) AS earned,
SUM(IF(entry_type = 'burn', coins, 0)) AS burned
FROM SuperCoinLedger
GROUP BY member_id, DATE_FORMAT(entry_at, '%Y-%m')
HAVING SUM(IF(entry_type = 'burn', coins, 0)) > SUM(IF(entry_type = 'earn', coins, 0))
ORDER BY member_id, ledger_month← Flipkart: Refund SLA Breaches by Product Category · Flipkart: Pincodes Where COD Parcels Keep Coming Back →