Every Rider's Favorite Driver
UrbanHop wants to experiment with letting riders "favorite" a driver for auto-matching, starting with whoever they've ridden with most.
riders
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Maya Chen | Austin | 2023-01-05 |
| 2 | Noah Patel | Denver | 2023-02-14 |
| 3 | Zara Ahmed | Austin | 2023-03-01 |
| 4 | Leo Kim | Seattle | 2023-04-20 |
| 5 | Ivy Brooks | Denver | 2023-05-11 |
rides
| 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 |
Task: Write a query that returns rider_name and driver_name: for each rider, the driver they have completed the most rides with. Cancelled rides do not count toward a pairing. A rider tied between two drivers gets both pairings back, on two rows. A rider with no completed rides has no favorite and does not appear at all. Rows come back in alphabetical order of rider_name, and a tied rider's two rows in alphabetical order of driver_name.
Example output — shape only, on invented names; Ruth Abara is level between two drivers, which is why she takes two rows.
| rider_name | driver_name |
|---|---|
| Dana Reyes | Tomas Aguilar |
| Ollie Kane | Kofi Mensah |
| Ruth Abara | Tomas Aguilar |
| Ruth Abara | Vera Lindqvist |
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