How Reliable Is Each Driver?
UrbanHop is building a driver scorecard, and reliability is the first line on it. Riders care less about how many trips a driver has done than about whether the trip they booked actually happens, so the measure the operations team wants is a proportion: of everything this driver was assigned, what share went ahead?
A proportion is awkward to get out of a log like this, because the two numbers it needs are counted over different sets of the same rows. The bottom half counts every ride assigned to the driver. The top half counts only the ones that went ahead — and those rows are not separated out anywhere, they are mixed in with the rest and told apart only by status.
rides — one row per ride. 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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, driver_id and completion_rate, with one row for every driver who appears in the log. completion_rate is the share of that driver's rides that went ahead — a value between 0 and 1 — rounded to 4 decimal places. A driver whose rides all went ahead shows 1. Rows come back most reliable first. Two drivers here finish on exactly the same rate, so settle that on driver_id, smallest first.
Example output — shape only, on invented drivers. A driver who never had one called off prints as 1, and the two on the same rate show how that tie is settled.
| driver_id | completion_rate |
|---|---|
| 6 | 1 |
| 7 | 0.8571 |
| 8 | 0.5 |
| 9 | 0.5 |
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