Whale Share of Monthly Store Revenue — SQL Problem
In free-to-play games a handful of whales bring in most of the money.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Dates
- Dialect: MySQL
- Problem: #186
Problem statement
In free-to-play games a handful of whales bring in most of the money. A player is a whale for a month when their purchases in that calendar month total at least ₹5,000. Finance wants to see how dependent each month is on its whales.
For every month that has at least one purchase, return purchase_month (as YYYY-MM), whales (the number of whale players that month), whale_revenue_inr (what they spent that month) and whale_share_pct = 100 × whale revenue ÷ the month's total revenue, rounded to 2 decimal places. A month without whales shows 0, 0 and 0. Order by purchase_month.
Tables
Table: Purchase
| Column | Type |
|---|---|
| purchase_id | int |
| player_id | int |
| amount_inr | int |
| purchased_at | datetime |
Primary key: purchase_id.
One row per completed in-app purchase; amount_inr is always positive.
Examples
Example 1
Purchase
| purchase_id | player_id | amount_inr | purchased_at |
|---|---|---|---|
| 1 | 31 | 1799 | 2025-01-03 20:15:00 |
| 2 | 31 | 1799 | 2025-01-18 21:40:00 |
| 3 | 31 | 1799 | 2025-01-31 23:10:00 |
| 4 | 32 | 499 | 2025-01-09 12:00:00 |
| 5 | 33 | 5000 | 2025-01-20 09:30:00 |
| 6 | 31 | 999 | 2025-02-01 10:00:00 |
| 7 | 32 | 449 | 2025-02-14 19:00:00 |
| 8 | 34 | 2500 | 2025-03-02 16:45:00 |
| 9 | 34 | 2499 | 2025-03-28 16:45:00 |
Output
| purchase_month | whales | whale_revenue_inr | whale_share_pct |
|---|---|---|---|
| 2025-01 | 2 | 10397 | 95.42 |
| 2025-02 | 0 | 0 | 0 |
| 2025-03 | 0 | 0 | 0 |
How to solve Whale Share of Monthly Store Revenue
The question has two levels of grouping. First (player, month): sum each player's purchases within a calendar month, keyed by DATE_FORMAT(purchased_at, '%Y-%m'). Player 31 spent three times ₹1,799 in January, ₹5,397 in all, so they are a January whale even though no single purchase was large; player 34's ₹4,999 in March misses the threshold by one rupee.
Then (month): over the per-player totals, SUM(spent) is the month's revenue, and CASE expressions keep only totals ≥ 5,000 for the whale count and whale revenue. The share is 100 * whale / total, rounded to two places. Because the month's total comes from the same rows, a month without whales simply gets 0 for all three — no special case.
The alternative builds the month totals and the whale-only totals separately and LEFT JOINs them, with COALESCE turning a missing whale row into 0; an inner join there would wrongly drop February. Both are two aggregations over the table.
Reference solution (MySQL)
WITH player_month AS (
SELECT DATE_FORMAT(purchased_at, '%Y-%m') AS purchase_month, player_id, SUM(amount_inr) AS spent
FROM Purchase
GROUP BY DATE_FORMAT(purchased_at, '%Y-%m'), player_id
)
SELECT purchase_month,
SUM(CASE WHEN spent >= 5000 THEN 1 ELSE 0 END) AS whales,
SUM(CASE WHEN spent >= 5000 THEN spent ELSE 0 END) AS whale_revenue_inr,
ROUND(100 * SUM(CASE WHEN spent >= 5000 THEN spent ELSE 0 END) / SUM(spent), 2) AS whale_share_pct
FROM player_month
GROUP BY purchase_month
ORDER BY purchase_monthAnother way
SELECT m.purchase_month, COALESCE(w.whales, 0) AS whales, COALESCE(w.rev, 0) AS whale_revenue_inr,
ROUND(100 * COALESCE(w.rev, 0) / m.total, 2) AS whale_share_pct
FROM (SELECT LEFT(purchased_at, 7) AS purchase_month, SUM(amount_inr) AS total FROM Purchase GROUP BY LEFT(purchased_at, 7)) m
LEFT JOIN (
SELECT purchase_month, COUNT(*) AS whales, SUM(spent) AS rev
FROM (SELECT LEFT(purchased_at, 7) AS purchase_month, player_id, SUM(amount_inr) AS spent
FROM Purchase GROUP BY LEFT(purchased_at, 7), player_id HAVING SUM(amount_inr) >= 5000) pm
GROUP BY purchase_month
) w ON w.purchase_month = m.purchase_month
ORDER BY m.purchase_month← Ranked Tier Demotions Between Weekly Snapshots · Guilds With Three Active Members in the Last Week →