Uber: Uber Pro Leaderboard Around Driver 107 in Pune

Medium
988.

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. 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

ColumnType
event_idint
driver_idint
cityvarchar
earned_ondate
pointsint

Primary key: event_id.

One row per points award this season (a completed trip, a peak-hour bonus, a five-star rating).

Example

The rows must come back in the order shown.

ProPoints

event_iddriver_idcityearned_onpoints
1101Pune2024-10-01120
2102Pune2024-10-0190
3103Pune2024-10-0275
4104Pune2024-10-0260
5104Pune2024-10-0315
6105Pune2024-10-0370
7107Pune2024-10-0450
8107Pune2024-10-0520
9106Pune2024-10-0540
10108Pune2024-10-0610
2 more rows

Output

leaderboard_rankdriver_idpoints
410475
510570
610770
710640
810810
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.

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

ColumnType
event_idint
driver_idint
cityvarchar
earned_ondate
pointsint

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_iddriver_idcityearned_onpoints
1101Pune2024-10-01120
2102Pune2024-10-0190
3103Pune2024-10-0275
4104Pune2024-10-0260
5104Pune2024-10-0315
6105Pune2024-10-0370
7107Pune2024-10-0450
8107Pune2024-10-0520
9106Pune2024-10-0540
10108Pune2024-10-0610
11109Mumbai2024-10-0672
12107Mumbai2024-10-07500

Output

leaderboard_rankdriver_idpoints
410475
510570
610770
710640
810810

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_rank

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