The Regulars
UrbanHop is testing a feature that lets a rider ask for a driver they have travelled with before, and the product team wants a case study to build the demo around: the rider and driver who have been matched together most often. Both names are needed, since the demo screen shows the pair.
Every booking counts towards a pairing, cancelled ones included — being matched is what the feature is about, and a cancellation still means the platform put those two together. Several pairs may well be level at the top of this data, so the tie rule is doing real work: the pair whose rider's name comes first alphabetically wins, and if two of those share a rider, the one whose driver's name comes first.
rides — one row per booking. rider_id points at a row in riders and driver_id at a row in drivers. distance_km is the trip length in kilometres and fare the price in dollars; both are recorded when the booking is made, so a booking that never happened still carries them. status is either completed or cancelled. ride_date is the day the ride was booked for.
| 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.0 | 8.0 | 2023-06-03 | completed |
| 3 | 2 | 2 | 10.0 | 22.0 | 2023-06-02 | completed |
| 4 | 3 | 1 | 2.1 | 6.5 | 2023-06-05 | cancelled |
| 5 | 3 | 4 | 4.4 | 11.0 | 2023-06-06 | completed |
| 6 | 4 | 3 | 7.8 | 18.0 | 2023-06-04 | completed |
| 7 | 5 | 2 | 1.5 | 5.0 | 2023-06-07 | cancelled |
| 8 | 2 | 2 | 12.3 | 26.5 | 2023-06-10 | completed |
| 9 | 1 | 1 | 6.6 | 15.0 | 2023-06-12 | completed |
| 10 | 4 | 3 | 3.3 | 9.0 | 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 |
riders — one row per registered passenger. city is the city they signed up in and signup_date the day they joined.
| 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 |
drivers — one row per driver on the platform. rating is their running star rating out of 5, and city is the city they drive in.
| id | name | city | rating |
|---|---|---|---|
| 1 | Sam Rios | Austin | 4.9 |
| 2 | Priya Desai | Denver | 4.6 |
| 3 | Oscar Lund | Seattle | 4.8 |
| 4 | Nina Cole | Austin | 4.2 |
All three tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns exactly one row, with the winning pair's rider name as rider, driver name as driver, and how many times they were matched as rides_together. If several pairs are level on the count, the one whose rider comes first alphabetically wins, and driver settles any pair still level after that.
Example output
If the winning pair turned out to be a rider called Cleo Barnes and a driver called Dana Fox, matched six times, you would get:
| rider | driver | rides_together |
|---|---|---|
| Cleo Barnes | Dana Fox | 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