Top In-App Spender in Each Region for 2025 — SQL Problem
Regional community managers want to thank their biggest spender of the year with a custom skin. Only purchases made in calendar year 2025 count.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #182
Problem statement
Regional community managers want to thank their biggest spender of the year with a custom skin. Only purchases made in calendar year 2025 count. For each region, find the player or players whose total 2025 spend is the highest in that region — if several players tie, return all of them. Regions where nobody bought anything in 2025 do not appear.
Return region, gamertag and total_spend_inr, ordered by region, then gamertag.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| region | varchar |
Primary key: player_id.
Gamertags are unique.
Table: Purchase
| Column | Type |
|---|---|
| purchase_id | int |
| player_id | int |
| amount_inr | int |
| purchased_on | date |
Primary key: purchase_id.
One row per completed in-app purchase.
Examples
Example 1
Player
| player_id | gamertag | region |
|---|---|---|
| 1 | DesiDragon | IN |
| 2 | OneTapAnanya | IN |
| 3 | LootGoblin | IN |
| 4 | SilentSara | EU |
| 5 | NovaNoah | EU |
| 6 | RocketRiya | SEA |
Purchase
| purchase_id | player_id | amount_inr | purchased_on |
|---|---|---|---|
| 1 | 1 | 1799 | 2025-01-14 |
| 2 | 1 | 449 | 2025-03-02 |
| 3 | 2 | 2248 | 2025-02-20 |
| 4 | 3 | 5000 | 2024-12-31 |
| 5 | 3 | 199 | 2025-01-01 |
| 6 | 4 | 999 | 2025-05-10 |
| 7 | 5 | 499 | 2025-06-01 |
| 8 | 6 | 3200 | 2024-11-11 |
Output
| region | gamertag | total_spend_inr |
|---|---|---|
| EU | SilentSara | 999 |
| IN | DesiDragon | 2248 |
| IN | OneTapAnanya | 2248 |
How to solve Top In-App Spender in Each Region for 2025
Two steps. First aggregate: filter purchases to 2025 (BETWEEN '2025-01-01' AND '2025-12-31' on a date column, or YEAR(purchased_on) = 2025), join to the player for the region and gamertag, and sum per player. The filter must run before the sum, otherwise a big purchase on 31 December 2024 would crown the wrong player — the example's LootGoblin.
Then pick the best per region. RANK() OVER (PARTITION BY region ORDER BY total DESC) numbers players inside each region and gives equal totals the same rank, so rnk = 1 returns every tied top spender; ROW_NUMBER would silently keep only one of them.
Without a ranking function, compare each total with the region's maximum through a correlated subquery over the same CTE, or attach MAX(SUM(...)) OVER (PARTITION BY region) in the grouped query itself. Regions without 2025 purchases have no rows after the filter, so they never appear. The cost is one aggregation plus a sort per region.
Reference solution (MySQL)
WITH spend AS (
SELECT p.region, p.gamertag, SUM(pu.amount_inr) AS total_spend_inr
FROM Purchase pu
JOIN Player p ON p.player_id = pu.player_id
WHERE pu.purchased_on BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY p.player_id, p.region, p.gamertag
), ranked AS (
SELECT region, gamertag, total_spend_inr,
RANK() OVER (PARTITION BY region ORDER BY total_spend_inr DESC) AS rnk
FROM spend
)
SELECT region, gamertag, total_spend_inr
FROM ranked
WHERE rnk = 1
ORDER BY region, gamertagAnother way
WITH spend AS (
SELECT p.region, p.gamertag, SUM(pu.amount_inr) AS total_spend_inr
FROM Purchase pu JOIN Player p ON p.player_id = pu.player_id
WHERE YEAR(pu.purchased_on) = 2025
GROUP BY p.player_id, p.region, p.gamertag
)
SELECT s.region, s.gamertag, s.total_spend_inr FROM spend s
WHERE s.total_spend_inr = (SELECT MAX(t.total_spend_inr) FROM spend t WHERE t.region = s.region)
ORDER BY s.region, s.gamertagAnother way
SELECT region, gamertag, total_spend_inr FROM (
SELECT p.region, p.gamertag, SUM(pu.amount_inr) AS total_spend_inr,
MAX(SUM(pu.amount_inr)) OVER (PARTITION BY p.region) AS best
FROM Purchase pu JOIN Player p ON p.player_id = pu.player_id
WHERE pu.purchased_on >= '2025-01-01' AND pu.purchased_on < '2026-01-01'
GROUP BY p.player_id, p.region, p.gamertag
) t WHERE total_spend_inr = best
ORDER BY region, gamertag← Player Win Rates With at Least Five Matches · Ranked Queue Wait Time by Hour of Day →