Battle Pass Race to Tier 30, Season by Season

Hard
193.

Battle Pass Race to Tier 30, Season by Season

Battle pass XP accumulates through a season and resets to zero when a new season starts. Tier 30 unlocks once a player's cumulative XP within the season reaches 30,000 (exactly 30,000 is enough). BattlePassXp holds each player's XP earned per day, tagged with the season.

For every (player, season) that reached tier 30, return player_id, season, reached_on (the first day the season's running total was at least 30,000) and days_taken (days from that player's first XP day of the season to reached_on; 0 if the same day). Order by season, then reached_on, then player_id.

Tables

Table: BattlePassXp

ColumnType
player_idint
earned_ondate
seasonvarchar
xpint

Primary key (together): player_id, earned_on.

A daily roll-up: one row per player per day they earned XP. Season S11 ran in January–February 2025 and S12 from March.

Example

The rows must come back in the order shown.

BattlePassXp

player_idearned_onseasonxp
12025-02-20S1112000
12025-02-25S1115000
12025-03-01S129000
12025-03-04S1221000
22025-02-27S1131000
22025-03-02S1214000
22025-03-03S1215999
32025-03-01S1210000
32025-03-02S1225000
32025-03-08S124000

Output

player_idseasonreached_ondays_taken
2S112025-02-270
3S122025-03-021
1S122025-03-043
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.

Battle Pass Race to Tier 30, Season by Season — SQL Problem

Battle pass XP accumulates through a season and resets to zero when a new season starts.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Subqueries
  • Dialect: MySQL
  • Problem: #193

Problem statement

Battle pass XP accumulates through a season and resets to zero when a new season starts. Tier 30 unlocks once a player's cumulative XP within the season reaches 30,000 (exactly 30,000 is enough). BattlePassXp holds each player's XP earned per day, tagged with the season.

For every (player, season) that reached tier 30, return player_id, season, reached_on (the first day the season's running total was at least 30,000) and days_taken (days from that player's first XP day of the season to reached_on; 0 if the same day). Order by season, then reached_on, then player_id.

Tables

Table: BattlePassXp

ColumnType
player_idint
earned_ondate
seasonvarchar
xpint

Primary key: player_id, earned_on.

A daily roll-up: one row per player per day they earned XP. Season S11 ran in January–February 2025 and S12 from March.

Examples

Example 1

BattlePassXp

player_idearned_onseasonxp
12025-02-20S1112000
12025-02-25S1115000
12025-03-01S129000
12025-03-04S1221000
22025-02-27S1131000
22025-03-02S1214000
22025-03-03S1215999
32025-03-01S1210000
32025-03-02S1225000
32025-03-08S124000

Output

player_idseasonreached_ondays_taken
2S112025-02-270
3S122025-03-021
1S122025-03-043

How to solve Battle Pass Race to Tier 30, Season by Season

The season reset is what a partitioned running total expresses: SUM(xp) OVER (PARTITION BY player_id, season ORDER BY earned_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives every daily row the XP earned so far in that season, starting again from zero in each new partition. Player 1 had 27,000 XP when S11 ended; carrying it over would wrongly unlock tier 30 on 1 March, but within S12 they reach 30,000 exactly on 4 March — and exactly 30,000 counts.

Once the running totals exist, the rows at or above 30,000 are the days after the unlock; the earliest of them is reached_on, so filter and take MIN(earned_on) per (player, season). Because XP is never negative the running total never falls back below the threshold, so the minimum is the unlock day. MIN(earned_on) OVER (PARTITION BY player_id, season) carries the season's first day along for DATEDIFF. Player 2 at 29,999 in S12 never appears.

Without windows, a correlated SUM over the earlier days of the same season computes each running total — quadratic per partition, fine for small tables. The window version is one sort per partition.

Reference solution (MySQL)

WITH running AS (
  SELECT player_id, season, earned_on,
         SUM(xp) OVER (PARTITION BY player_id, season ORDER BY earned_on
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS season_xp,
         MIN(earned_on) OVER (PARTITION BY player_id, season) AS season_start
  FROM BattlePassXp
)
SELECT player_id, season, MIN(earned_on) AS reached_on,
       DATEDIFF(MIN(earned_on), MIN(season_start)) AS days_taken
FROM running
WHERE season_xp >= 30000
GROUP BY player_id, season
ORDER BY season, reached_on, player_id

Another way

SELECT b.player_id, b.season, MIN(b.earned_on) AS reached_on,
       DATEDIFF(MIN(b.earned_on), (SELECT MIN(s.earned_on) FROM BattlePassXp s WHERE s.player_id = b.player_id AND s.season = b.season)) AS days_taken
FROM BattlePassXp b
WHERE (SELECT SUM(e.xp) FROM BattlePassXp e
       WHERE e.player_id = b.player_id AND e.season = b.season AND e.earned_on <= b.earned_on) >= 30000
GROUP BY b.player_id, b.season
ORDER BY season, reached_on, b.player_id

← Day-1 and Day-7 Retention by Signup Week · Peak Concurrent Game Sessions per Server Region →