Flipkart: Months Members Burned More SuperCoins Than Earned

Medium
657.

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

ColumnTypeValues
entry_idint
member_idint
entry_atdatetime
coinsint
entry_typeenumearn, burn, expire

Primary key: entry_id.

One row per ledger entry; coins is always positive — the type says which way it moved.

Example

The rows must come back in the order shown.

SuperCoinLedger

entry_idmember_identry_atcoinsentry_type
172024-07-03 10:15:00120earn
272024-07-21 19:02:11200burn
372024-08-02 08:00:0050earn
472024-08-30 22:41:0950burn
592024-07-11 13:30:00300expire
692024-07-12 09:05:4440burn
792024-08-01 00:00:00500earn

Output

member_idledger_monthearnedburned
72024-07120200
92024-07040
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: 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

ColumnType
entry_idint
member_idint
entry_atdatetime
coinsint
entry_typeenum(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_idmember_identry_atcoinsentry_type
172024-07-03 10:15:00120earn
272024-07-21 19:02:11200burn
372024-08-02 08:00:0050earn
472024-08-30 22:41:0950burn
592024-07-11 13:30:00300expire
692024-07-12 09:05:4440burn
792024-08-01 00:00:00500earn

Output

member_idledger_monthearnedburned
72024-07120200
92024-07040

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_month

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