How Much Has Each Rider Spent?
UrbanHop is reconciling the month's rider billing against what the payment processor actually charged, and the first thing finance needs is its own figure: what each rider should have been billed.
The ride log is the only record, and it holds rides that never happened alongside rides that did. A cancelled ride still carries the fare it was quoted at, because that is what the row recorded when the trip was booked — but nobody is charged for a trip that did not run, so those rows must not reach the total. The log identifies riders by number and the billing statement needs names, which are kept on the rider record.
riders — one row per registered rider. city is the city they signed up in.
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Maya Chen | Austin | 2023-01-05 |
| 2 | Noah Patel | Denver | 2023-02-14 |
| 3 | Zara Ahmed | Austin | 2023-03-01 |
| 4 | Leo Kim | Seattle | 2023-04-20 |
| 5 | Ivy Brooks | Denver | 2023-05-11 |
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_spent, with one row per rider, adding up the fare on that rider's rides that actually went ahead. Cancelled rides contribute nothing. A rider whose every ride was called off produces no row at all rather than a row reading zero. Rows come back highest-spending first, ties settled on name alphabetically.
Example output — shape only, on invented riders and totals.
| name | total_spent |
|---|---|
| Ruth Abara | 96.5 |
| Ollie Kane | 73 |
| Dana Reyes | 12.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