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
| Column | Type |
|---|---|
| team_id | int |
| snapshot_no | int |
| points | decimal |
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_id | snapshot_no | points |
|---|---|---|
| 1 | 1 | 40 |
| 2 | 1 | 35 |
| 3 | 1 | 30 |
| 4 | 1 | 12 |
| 1 | 2 | 44 |
| 2 | 2 | 60 |
| 3 | 2 | 31 |
| 4 | 2 | 50.5 |
| 1 | 3 | 70 |
| 2 | 3 | 62 |
| 3 | 3 | 88 |
| 4 | 3 | 51 |
| 1 | 4 | 71 |
| 2 | 4 | 90 |
| 3 | 4 | 90 |
| 4 | 4 | 52 |
Output
| team_id | best_climb | snapshot_no |
|---|---|---|
| 3 | 3 | 3 |
| 2 | 2 | 4 |
| 4 | 2 | 2 |
| 1 | 1 | 3 |
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_idAnother 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 →