Whale Share of Monthly Store Revenue

Medium
186.

Whale Share of Monthly Store Revenue

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

ColumnType
purchase_idint
player_idint
amount_inrint
purchased_atdatetime

Primary key: purchase_id.

One row per completed in-app purchase; amount_inr is always positive.

Example

The rows must come back in the order shown.

Purchase

purchase_idplayer_idamount_inrpurchased_at
13117992025-01-03 20:15:00
23117992025-01-18 21:40:00
33117992025-01-31 23:10:00
4324992025-01-09 12:00:00
53350002025-01-20 09:30:00
6319992025-02-01 10:00:00
7324492025-02-14 19:00:00
83425002025-03-02 16:45:00
93424992025-03-28 16:45:00

Output

purchase_monthwhaleswhale_revenue_inrwhale_share_pct
2025-0121039795.42
2025-02000
2025-03000
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.

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

ColumnType
purchase_idint
player_idint
amount_inrint
purchased_atdatetime

Primary key: purchase_id.

One row per completed in-app purchase; amount_inr is always positive.

Examples

Example 1

Purchase

purchase_idplayer_idamount_inrpurchased_at
13117992025-01-03 20:15:00
23117992025-01-18 21:40:00
33117992025-01-31 23:10:00
4324992025-01-09 12:00:00
53350002025-01-20 09:30:00
6319992025-02-01 10:00:00
7324492025-02-14 19:00:00
83425002025-03-02 16:45:00
93424992025-03-28 16:45:00

Output

purchase_monthwhaleswhale_revenue_inrwhale_share_pct
2025-0121039795.42
2025-02000
2025-03000

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_month

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