UrbanHop's Most Frequent Rider
UrbanHop, a city ride-hailing app, is posting a small loyalty gift to its busiest rider of the month. Marketing needs one name and the number of trips behind it, so the note can say why the gift arrived.
Two tables are in play, and neither can answer on its own. Every trip is recorded in rides, which knows only a rider's id number — no names. The names live in riders. A trip counts towards the total whether or not it went ahead: a booking that the rider later cancelled still shows they opened the app.
riders — one row per registered rider.
| 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 — one row per trip booked. rider_id points at a row in riders. status is completed or cancelled. fare is in dollars.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single row with two columns, name and num_rides, for the rider with the most trips of any status. Two riders are level at the top of this data, so break the tie in favour of the smaller id in riders — the one who signed up first.
Example output — shape only, on an invented rider and count.
| name | num_rides |
|---|---|
| Ruth Abara | 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