Which Riders Cancel the Most?
UrbanHop knows its company-wide cancellation figure and it has stopped being informative: a quarter of rides cancelled could mean everybody cancels occasionally, or it could mean most riders never cancel and one or two abandon almost every trip. Those two worlds call for completely different responses, and only a per-rider breakdown can tell them apart.
The measure has to be a proportion rather than a count, because riders take very different numbers of trips. One cancellation out of two is a habit; one out of ten is an accident. Note also that a rider's cancelled trips still count towards how many trips they took — the denominator is everything they booked, not everything they completed.
rides — one row per ride booked. rider_id is the rider who booked it. 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 two columns, rider_id and cancel_rate, with one row per rider who has booked at least one ride. cancel_rate is the number of that rider's rides with a status of cancelled divided by the total number of rides they booked, cancelled ones included — a fraction between 0 and 1, not a percentage, rounded to 4 decimal places. A rider who has never cancelled comes back as 0 rather than being left out. A rider who has never booked anything does not appear at all. Rows come back sorted by rider_id, smallest first.
Example output — shape only, on invented riders; rider 6 never cancelled, which is why the figure is 0 rather than missing.
| rider_id | cancel_rate |
|---|---|
| 6 | 0 |
| 7 | 0.25 |
| 9 | 0.8333 |
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