Ranked Tier Demotions Between Weekly Snapshots — SQL Problem
Every week the ranked service stores a snapshot of each active player's tier; a player who did not play that week has no snapshot.
- Difficulty: Medium
- Topics: Window Functions, Joins
- Dialect: MySQL
- Problem: #185
Problem statement
Every week the ranked service stores a snapshot of each active player's tier; a player who did not play that week has no snapshot. The tiers are ordered by RankTier.tier_order (Bronze lowest). A demotion is a snapshot whose tier is lower than the tier in the same player's previous snapshot — however many weeks apart they are.
Return every demotion with the columns player_id, snapshot_date, from_tier and to_tier, ordered by player_id, then snapshot_date.
Tables
Table: RankTier
| Column | Type |
|---|---|
| tier_name | varchar |
| tier_order | int |
Primary key: tier_name.
The ranked ladder: a higher tier_order is a higher tier.
Table: RankSnapshot
| Column | Type |
|---|---|
| player_id | int |
| snapshot_date | date |
| tier_name | varchar |
Primary key: player_id, snapshot_date.
One row per player per weekly snapshot they appear in. tier_name is always in RankTier.
Examples
Example 1
RankTier
| tier_name | tier_order |
|---|---|
| Bronze | 1 |
| Silver | 2 |
| Gold | 3 |
| Platinum | 4 |
| Diamond | 5 |
| Master | 6 |
RankSnapshot
| player_id | snapshot_date | tier_name |
|---|---|---|
| 1 | 2025-01-06 | Gold |
| 1 | 2025-01-13 | Platinum |
| 1 | 2025-01-20 | Gold |
| 1 | 2025-02-10 | Silver |
| 2 | 2025-01-06 | Diamond |
| 2 | 2025-01-13 | Diamond |
| 2 | 2025-01-27 | Master |
| 3 | 2025-01-13 | Bronze |
| 3 | 2025-01-20 | Silver |
Output
| player_id | snapshot_date | from_tier | to_tier |
|---|---|---|---|
| 1 | 2025-01-20 | Platinum | Gold |
| 1 | 2025-02-10 | Gold | Silver |
How to solve Ranked Tier Demotions Between Weekly Snapshots
Each snapshot must be compared with the one just before it for the same player. LAG(...) OVER (PARTITION BY player_id ORDER BY snapshot_date) fetches the previous row's values within each player's history, so a gap of several weeks without snapshots is skipped naturally — player 1 went from Gold on 20 January straight to Silver on 10 February, which is a demotion even though three weeks passed.
Tier names cannot be compared as strings (Gold < Silver alphabetically, but Gold is higher), so join RankTier first and carry tier_order alongside the name; LAG both. A demotion is tier_order < prev_order. On a player's first snapshot prev_order is NULL, the comparison is unknown, and the row is dropped. Staying in the same tier is not a demotion.
Without window functions, find the previous snapshot with a correlated MAX(snapshot_date) < current and join to it, then join the ladder twice. That is a lookup per row; the window version sorts each player's snapshots once.
Reference solution (MySQL)
WITH ordered AS (
SELECT s.player_id, s.snapshot_date, s.tier_name, t.tier_order,
LAG(s.tier_name) OVER (PARTITION BY s.player_id ORDER BY s.snapshot_date) AS prev_tier,
LAG(t.tier_order) OVER (PARTITION BY s.player_id ORDER BY s.snapshot_date) AS prev_order
FROM RankSnapshot s
JOIN RankTier t ON t.tier_name = s.tier_name
)
SELECT player_id, snapshot_date, prev_tier AS from_tier, tier_name AS to_tier
FROM ordered
WHERE tier_order < prev_order
ORDER BY player_id, snapshot_dateAnother way
SELECT cur.player_id, cur.snapshot_date, prev.tier_name AS from_tier, cur.tier_name AS to_tier
FROM RankSnapshot cur
JOIN RankSnapshot prev ON prev.player_id = cur.player_id
AND prev.snapshot_date = (SELECT MAX(p.snapshot_date) FROM RankSnapshot p
WHERE p.player_id = cur.player_id AND p.snapshot_date < cur.snapshot_date)
JOIN RankTier tc ON tc.tier_name = cur.tier_name
JOIN RankTier tp ON tp.tier_name = prev.tier_name
WHERE tc.tier_order < tp.tier_order
ORDER BY cur.player_id, cur.snapshot_date← Players Reported for Cheating by Three Reporters in a Month · Whale Share of Monthly Store Revenue →