Dream11: Biggest Live Leaderboard Climb of Each Team

Hard
647.

Dream11: Biggest Live Leaderboard Climb of Each Team

During a match, Dream11's live leaderboard re-ranks every team in a contest after each scoring update. The engagement team wants to celebrate dramatic comebacks. At each snapshot a team's position is its rank by points (highest first; equal points share a position, as in RANK). A climb is the previous snapshot's position minus the current one.

For every team that ever climbed, return team_id, best_climb (its largest climb) and snapshot_no (the snapshot where that climb happened — the earliest one if several share the largest climb). Teams that never moved up are left out. Order by best_climb descending, then by team_id.

A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.

Tables

Table: LiveScore

ColumnType
team_idint
snapshot_noint
pointsdecimal

Primary key (together): team_id, snapshot_no.

A team's total points in one contest after each update. Snapshots are numbered 1, 2, 3, … with no gaps, and every team has a row at every snapshot.

Example

The rows must come back in the order shown.

LiveScore

team_idsnapshot_nopoints
1140
2135
3130
4112
1244
2260
3231
4250.5
1370
2362
6 more rows

Output

team_idbest_climbsnapshot_no
333
224
422
113
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.

Dream11: Biggest Live Leaderboard Climb of Each Team

During a match, Dream11's live leaderboard re-ranks every team in a contest after each scoring update.

  • Difficulty: Hard
  • Topics: Window Functions
  • Dialect: MySQL
  • Problem: #647

Problem statement

During a match, Dream11's live leaderboard re-ranks every team in a contest after each scoring update. The engagement team wants to celebrate dramatic comebacks. At each snapshot a team's position is its rank by points (highest first; equal points share a position, as in RANK). A climb is the previous snapshot's position minus the current one.

For every team that ever climbed, return team_id, best_climb (its largest climb) and snapshot_no (the snapshot where that climb happened — the earliest one if several share the largest climb). Teams that never moved up are left out. Order by best_climb descending, then by team_id.

A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.

Tables

Table: LiveScore

ColumnType
team_idint
snapshot_noint
pointsdecimal

Primary key: team_id, snapshot_no.

A team's total points in one contest after each update. Snapshots are numbered 1, 2, 3, … with no gaps, and every team has a row at every snapshot.

Examples

Example 1

LiveScore

team_idsnapshot_nopoints
1140
2135
3130
4112
1244
2260
3231
4250.5
1370
2362
3388
4351
1471
2490
3490
4452

Output

team_idbest_climbsnapshot_no
333
224
422
113

How to solve Dream11: Biggest Live Leaderboard Climb of Each Team

The positions are not stored, so the first step ranks the teams inside every snapshot: RANK() OVER (PARTITION BY snapshot_no ORDER BY points DESC). RANK gives level teams the same position, matching how the leaderboard displays a tie.

The second step changes the partition: now each team's own history matters, so LAG(pos) OVER (PARTITION BY team_id ORDER BY snapshot_no) puts the previous position beside the current one. A climb is prev_pos − pos. Only real climbs count, so rows with prev_pos > pos are kept — which also drops the first snapshot (no previous position) and avoids subtracting into negative numbers, which MySQL refuses for its unsigned ranking results. Finally ROW_NUMBER() per team, ordered by climb descending and then snapshot ascending, picks the biggest climb and its earliest occurrence in one deterministic step.

The alternative ranks with a correlated count (one plus the teams strictly ahead), pairs consecutive snapshots with a self join on snapshot_no - 1 — valid because snapshots have no gaps — and keeps the climb that no other climb of the team beats under the same ordering, with NOT EXISTS. The window version costs a few sorts; the correlated one is quadratic per snapshot.

Reference solution (MySQL)

WITH positioned AS (
  SELECT team_id, snapshot_no,
         RANK() OVER (PARTITION BY snapshot_no ORDER BY points DESC) AS pos
  FROM LiveScore
),
moves AS (
  SELECT team_id, snapshot_no, pos,
         LAG(pos) OVER (PARTITION BY team_id ORDER BY snapshot_no) AS prev_pos
  FROM positioned
),
climbs AS (
  SELECT team_id, snapshot_no, prev_pos - pos AS climb,
         ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY prev_pos - pos DESC, snapshot_no) AS k
  FROM moves
  WHERE prev_pos > pos
)
SELECT team_id, climb AS best_climb, snapshot_no
FROM climbs
WHERE k = 1
ORDER BY best_climb DESC, team_id

Another way

WITH positioned AS (
  SELECT a.team_id, a.snapshot_no,
         1 + (SELECT COUNT(*) FROM LiveScore b WHERE b.snapshot_no = a.snapshot_no AND b.points > a.points) AS pos
  FROM LiveScore a
),
climbs AS (
  SELECT cur.team_id, cur.snapshot_no, prv.pos - cur.pos AS climb
  FROM positioned cur
  JOIN positioned prv ON prv.team_id = cur.team_id AND prv.snapshot_no = cur.snapshot_no - 1
  WHERE prv.pos > cur.pos
)
SELECT c.team_id, c.climb AS best_climb, c.snapshot_no
FROM climbs c
WHERE NOT EXISTS (SELECT 1 FROM climbs d WHERE d.team_id = c.team_id
                    AND (d.climb > c.climb OR (d.climb = c.climb AND d.snapshot_no < c.snapshot_no)))
ORDER BY best_climb DESC, team_id

← Dream11: Second-Paid-Contest Activation Rate by Signup Month · Flipkart: Plus Members Sitting on Unused SuperCoins →