Which Riders Have Had a Bad Experience?
Support at UrbanHop wants to get ahead of the complaints. Anyone whose ride has fallen through gets a short apology and a credit, and the team needs the list of people to send it to before the weekend.
There is a wrinkle worth thinking about before writing anything. The ride log holds one row per ride, and an unlucky rider can appear on it several times over — but they should receive one apology, not three. So the answer is a list of people, even though the evidence is a list of rides.
rides — one row per ride. status records how the ride ended; fare is in dollars and is recorded whether or not the ride went ahead.
| 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 with at least one ride that did not go ahead. Each qualifying rider appears exactly once however many such rides they have. Hand the ids back smallest first.
Example output — shape only, on invented ids.
| rider_id |
|---|
| 4 |
| 7 |
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