Who's Our Runner-Up Driver?
UrbanHop gives out a driver-of-the-month award and has decided the runner-up should be recognised too. The top earner is already known; the operations team needs the driver standing second on total earnings.
Two things decide the answer. A cancelled ride pays the driver nothing, so those fares are not earnings — and the gap between second and third place is fifty cents, close enough that letting a single cancelled fare through changes who gets the certificate. And "second" is not a fact about any driver's rows: it only exists once every driver's total has been worked out and the totals put beside one another.
rides — one row per ride. driver_id is the driver who took it. fare is what the ride charged, in dollars. status is either completed or cancelled.
| 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 | 8 | 2023-06-03 | completed |
| 3 | 2 | 2 | 10 | 22 | 2023-06-02 | completed |
| 4 | 3 | 1 | 2.1 | 6.5 | 2023-06-05 | cancelled |
| 5 | 3 | 4 | 4.4 | 11 | 2023-06-06 | completed |
| 6 | 4 | 3 | 7.8 | 18 | 2023-06-04 | completed |
| 7 | 5 | 2 | 1.5 | 5 | 2023-06-07 | cancelled |
| 8 | 2 | 2 | 12.3 | 26.5 | 2023-06-10 | completed |
| 9 | 1 | 1 | 6.6 | 15 | 2023-06-12 | completed |
| 10 | 4 | 3 | 3.3 | 9 | 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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning exactly one row with two columns, driver_id and total_earnings — the driver standing second when every driver is arranged by total completed-ride fare, largest first, and that driver's total. Cancelled rides contribute nothing to any total. total_earnings is an exact dollar figure and is not rounded. "Second" is positional: if two drivers were tied at the top, this would return the second of that pair rather than the next lower total.
Example output — shape only, on an invented driver and total.
| driver_id | total_earnings |
|---|---|
| 7 | 96.25 |
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