Who's Ridden With More Than One Driver?
The matching team at UrbanHop is looking at how the dispatcher behaves in each city. In a thin market a rider keeps getting the same driver, which is comfortable up to a point and a problem after it — one driver going offline takes that rider's service with them. The first thing the team wants is the riders who have seen any variety at all, picked out.
The ride log makes this less obvious than it sounds. It records one row per ride, so a rider with three rides could have had three different drivers or the same driver three times, and the row count cannot tell those apart. It is also worth deciding what to do about rides that never ran: a cancelled ride was still a pairing the dispatcher made, and the team is studying the dispatcher.
rides — one row per ride. driver_id is the driver the dispatcher assigned; 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 a single column, rider_id, holding every rider who has been assigned at least two different driver_id values. Every ride counts as a pairing whether or not it went ahead. Somebody who has ridden many times with one driver does not qualify. Hand the ids back smallest first.
Example output — shape only, on invented ids.
| rider_id |
|---|
| 4 |
| 6 |
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