Uber: Uber Pro Leaderboard Around Driver 107 in Pune
The Uber Pro screen does not show a driver the whole city leaderboard — only the drivers just above and just below them, which is more motivating.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #988
Problem statement
The Uber Pro screen does not show a driver the whole city leaderboard — only the drivers just above and just below them, which is more motivating. Build that view for driver 107 in Pune.
A driver's score is the sum of their points from Pune rows of ProPoints; drivers with no Pune rows are not on the board. Rank by points descending, ties broken by the smaller driver_id, so every rank is distinct (1, 2, 3, …). Return leaderboard_rank, driver_id and points for every driver whose rank is within 2 of driver 107's (driver 107 included), ordered by leaderboard_rank. If driver 107 has no Pune points, return no rows.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: ProPoints
| Column | Type |
|---|---|
| event_id | int |
| driver_id | int |
| city | varchar |
| earned_on | date |
| points | int |
Primary key: event_id.
One row per points award this season (a completed trip, a peak-hour bonus, a five-star rating).
Examples
Example 1
ProPoints
| event_id | driver_id | city | earned_on | points |
|---|---|---|---|---|
| 1 | 101 | Pune | 2024-10-01 | 120 |
| 2 | 102 | Pune | 2024-10-01 | 90 |
| 3 | 103 | Pune | 2024-10-02 | 75 |
| 4 | 104 | Pune | 2024-10-02 | 60 |
| 5 | 104 | Pune | 2024-10-03 | 15 |
| 6 | 105 | Pune | 2024-10-03 | 70 |
| 7 | 107 | Pune | 2024-10-04 | 50 |
| 8 | 107 | Pune | 2024-10-05 | 20 |
| 9 | 106 | Pune | 2024-10-05 | 40 |
| 10 | 108 | Pune | 2024-10-06 | 10 |
| 11 | 109 | Mumbai | 2024-10-06 | 72 |
| 12 | 107 | Mumbai | 2024-10-07 | 500 |
Output
| leaderboard_rank | driver_id | points |
|---|---|---|
| 4 | 104 | 75 |
| 5 | 105 | 70 |
| 6 | 107 | 70 |
| 7 | 106 | 40 |
| 8 | 108 | 10 |
How to solve Uber: Uber Pro Leaderboard Around Driver 107 in Pune
The task is a window around one entity: rank everyone, locate the entity's rank, keep the neighbouring ranks. Ranking needs the whole board, so the filter on driver 107 must come after the ranking — filtering first would leave 107 alone at rank 1.
The board CTE totals Pune points per driver and ranks them in the same step: a window function may order by an aggregate, so ROW_NUMBER() OVER (ORDER BY SUM(points) DESC, driver_id) ranks the grouped rows. Mumbai rows are filtered out before grouping, so driver 107's 500 Mumbai points do not lift them in Pune. In the example, 103 and 104 both have 75 points and the smaller id, 103, ranks third; 105 and 107 both have 70, so 105 is fifth and 107 sixth. The answer is ranks 4 to 8.
Joining the board to driver 107's own row (me) puts 107's rank on every row, and the neighbours are the ranks between me − 2 and me + 2. The condition is written as b.leaderboard_rank + 2 >= me.leaderboard_rank rather than >= me.leaderboard_rank - 2: MySQL's ROW_NUMBER() is unsigned, and subtracting 2 from rank 1 would overflow. If 107 has no Pune points the join finds no me row and the result is empty.
The alternative ranks with a correlated count (drivers with more points, or equal points and a smaller id) and compares distances with ABS — safe there because the counted rank is signed. Both read the points once; the window version sorts the board once.
Reference solution (MySQL)
WITH board AS (
SELECT driver_id, SUM(points) AS points,
ROW_NUMBER() OVER (ORDER BY SUM(points) DESC, driver_id) AS leaderboard_rank
FROM ProPoints
WHERE city = 'Pune'
GROUP BY driver_id
)
SELECT b.leaderboard_rank, b.driver_id, b.points
FROM board b
JOIN board me ON me.driver_id = 107
WHERE b.leaderboard_rank + 2 >= me.leaderboard_rank
AND b.leaderboard_rank <= me.leaderboard_rank + 2
ORDER BY b.leaderboard_rankAnother way
WITH totals AS (
SELECT driver_id, SUM(points) AS points FROM ProPoints WHERE city = 'Pune' GROUP BY driver_id
), board AS (
SELECT t.driver_id, t.points,
1 + (SELECT COUNT(*) FROM totals o
WHERE o.points > t.points OR (o.points = t.points AND o.driver_id < t.driver_id)) AS leaderboard_rank
FROM totals t
)
SELECT leaderboard_rank, driver_id, points
FROM board
WHERE ABS(leaderboard_rank - (SELECT leaderboard_rank FROM board WHERE driver_id = 107)) <= 2
ORDER BY leaderboard_rank← Uber: Rider Referrals That Qualified for the Bonus · Uber: UberX Share Requests Matched With a Co-Rider →