The Driver Leaderboard
Every Monday the UrbanHop city GM posts a driver leaderboard by earnings, and the drivers read it closely enough that the details matter. Two drivers who earned exactly the same amount get the same position — the GM will not invent a difference between them — and when that happens the position immediately after is skipped, so a board can read 1, 2, 2, 4.
Earnings means the fares of trips actually delivered. Cancelled bookings put nothing in anybody's pocket. The board shows names and money to two decimal places, and a driver who delivered nothing has no earnings and is not on it.
rides — one row per booking. rider_id points at a row in riders and driver_id at a row in drivers. distance_km is the trip length in kilometres and fare the price in dollars; both are recorded when the booking is made, so a booking that never happened still carries them. status is either completed or cancelled. ride_date is the day the ride was booked for.
| id | rider_id | driver_id | distance_km | fare | ride_date | status |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 5.2 | 12.5 | 2023-06-01 | completed |
| 2 | 1 | 4 | 3.0 | 8.0 | 2023-06-03 | completed |
| 3 | 2 | 2 | 10.0 | 22.0 | 2023-06-02 | completed |
| 4 | 3 | 1 | 2.1 | 6.5 | 2023-06-05 | cancelled |
| 5 | 3 | 4 | 4.4 | 11.0 | 2023-06-06 | completed |
| 6 | 4 | 3 | 7.8 | 18.0 | 2023-06-04 | completed |
| 7 | 5 | 2 | 1.5 | 5.0 | 2023-06-07 | cancelled |
| 8 | 2 | 2 | 12.3 | 26.5 | 2023-06-10 | completed |
| 9 | 1 | 1 | 6.6 | 15.0 | 2023-06-12 | completed |
| 10 | 4 | 3 | 3.3 | 9.0 | 2023-06-15 | completed |
| 11 | 5 | 2 | 8.8 | 19.5 | 2023-06-18 | completed |
| 12 | 3 | 4 | 2.9 | 7.5 | 2023-06-20 | cancelled |
drivers — one row per driver on the platform. rating is their running star rating out of 5, and city is the city they drive in.
| id | name | city | rating |
|---|---|---|---|
| 1 | Sam Rios | Austin | 4.9 |
| 2 | Priya Desai | Denver | 4.6 |
| 3 | Oscar Lund | Seattle | 4.8 |
| 4 | Nina Cole | Austin | 4.2 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns one row per driver with at least one completed trip, with their name, their earnings — the total of the fares of trips they delivered, rounded to two decimal places — and their position on the board as earnings_rank, 1 being the highest earner. Drivers on identical earnings share a position and the next position is skipped. Best position first; drivers sharing a position are separated alphabetically by name.
Example output
Shape only — these drivers and figures are invented. Two of them are level, so they share position 2 and position 3 goes unused:
| name | earnings | earnings_rank |
|---|---|---|
| Dana Fox | 210.5 | 1 |
| Milo Reyes | 96 | 2 |
| Rosa Vega | 96 | 2 |
| Tariq Bell | 40.75 | 4 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in