Ranked Tier Demotions Between Weekly Snapshots

Medium
185.

Ranked Tier Demotions Between Weekly Snapshots

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

ColumnType
tier_namevarchar
tier_orderint

Primary key: tier_name.

The ranked ladder: a higher tier_order is a higher tier.

Table: RankSnapshot

ColumnType
player_idint
snapshot_datedate
tier_namevarchar

Primary key (together): player_id, snapshot_date.

One row per player per weekly snapshot they appear in. tier_name is always in RankTier.

Example

The rows must come back in the order shown.

RankTier

tier_nametier_order
Bronze1
Silver2
Gold3
Platinum4
Diamond5
Master6

RankSnapshot

player_idsnapshot_datetier_name
12025-01-06Gold
12025-01-13Platinum
12025-01-20Gold
12025-02-10Silver
22025-01-06Diamond
22025-01-13Diamond
22025-01-27Master
32025-01-13Bronze
32025-01-20Silver

Output

player_idsnapshot_datefrom_tierto_tier
12025-01-20PlatinumGold
12025-02-10GoldSilver
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.

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

ColumnType
tier_namevarchar
tier_orderint

Primary key: tier_name.

The ranked ladder: a higher tier_order is a higher tier.

Table: RankSnapshot

ColumnType
player_idint
snapshot_datedate
tier_namevarchar

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_nametier_order
Bronze1
Silver2
Gold3
Platinum4
Diamond5
Master6

RankSnapshot

player_idsnapshot_datetier_name
12025-01-06Gold
12025-01-13Platinum
12025-01-20Gold
12025-02-10Silver
22025-01-06Diamond
22025-01-13Diamond
22025-01-27Master
32025-01-13Bronze
32025-01-20Silver

Output

player_idsnapshot_datefrom_tierto_tier
12025-01-20PlatinumGold
12025-02-10GoldSilver

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_date

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