Who Suffers the Cancellations?
Cancellations cost UrbanHop drivers time they are not paid for — they have often already set off — and the operations team wants to know whether that cost is falling evenly or landing repeatedly on the same people. A driver with one cancellation out of ten bookings has had an ordinary week. One with one out of two is being sent to a corner of the city nobody wants.
So the report needs both figures side by side for each driver: everything they were booked for, and how much of it fell through. Every driver on the platform has been booked at least once, so nobody is missing.
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 their name, how many bookings they received in total as booked, and how many of those were cancelled as cancelled. Most cancellations first; drivers on the same number of cancellations are separated alphabetically by name.
Example output
Shape only — these drivers and counts are invented:
| name | booked | cancelled |
|---|---|---|
| Dana Fox | 12 | 4 |
| Rosa Vega | 9 | 4 |
| Milo Reyes | 7 | 2 |
| Tariq Bell | 5 | 0 |
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