Top In-App Spender in Each Region for 2025

Medium
182.

Top In-App Spender in Each Region for 2025

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

ColumnType
player_idint
gamertagvarchar
regionvarchar

Primary key: player_id.

Gamertags are unique.

Table: Purchase

ColumnType
purchase_idint
player_idint
amount_inrint
purchased_ondate

Primary key: purchase_id.

One row per completed in-app purchase.

Example

The rows must come back in the order shown.

Player

player_idgamertagregion
1DesiDragonIN
2OneTapAnanyaIN
3LootGoblinIN
4SilentSaraEU
5NovaNoahEU
6RocketRiyaSEA

Purchase

purchase_idplayer_idamount_inrpurchased_on
1117992025-01-14
214492025-03-02
3222482025-02-20
4350002024-12-31
531992025-01-01
649992025-05-10
754992025-06-01
8632002024-11-11

Output

regiongamertagtotal_spend_inr
EUSilentSara999
INDesiDragon2248
INOneTapAnanya2248
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.

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

ColumnType
player_idint
gamertagvarchar
regionvarchar

Primary key: player_id.

Gamertags are unique.

Table: Purchase

ColumnType
purchase_idint
player_idint
amount_inrint
purchased_ondate

Primary key: purchase_id.

One row per completed in-app purchase.

Examples

Example 1

Player

player_idgamertagregion
1DesiDragonIN
2OneTapAnanyaIN
3LootGoblinIN
4SilentSaraEU
5NovaNoahEU
6RocketRiyaSEA

Purchase

purchase_idplayer_idamount_inrpurchased_on
1117992025-01-14
214492025-03-02
3222482025-02-20
4350002024-12-31
531992025-01-01
649992025-05-10
754992025-06-01
8632002024-11-11

Output

regiongamertagtotal_spend_inr
EUSilentSara999
INDesiDragon2248
INOneTapAnanya2248

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, gamertag

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

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