How Much Has Each Driver Earned?
Payouts go out at UrbanHop on the last working day of the month, and the finance team builds the run from the ride log. A driver is paid for the trips that actually happened; a trip that was called off before it ran earns nobody anything, even though it still sits in the log with a fare attached to it.
That fare on a cancelled row is the trap. It is there because the fare was quoted, not because it was collected, and a payout run that adds up every row pays drivers for work they did not do. The log also identifies drivers by number, while the payment file needs names, and names live on the driver record.
drivers — one row per driver. rating is their average score out of 5.
| 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 |
rides — one row per ride. fare is in dollars and is recorded whether or not the ride went ahead; status records how the ride ended.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, name and total_earnings, with one row per driver, adding up the fare on that driver's rides that actually went ahead. Cancelled rides contribute nothing. A driver whose every ride was called off produces no row at all rather than a row reading zero. Rows come back highest-earning first, ties settled on name alphabetically.
Example output — shape only, on invented drivers and totals.
| name | total_earnings |
|---|---|
| Vera Lindqvist | 142.5 |
| Tomas Aguilar | 88 |
| Kofi Mensah | 31.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