Personal Bests
The UrbanHop driver app has a small stats card, and the line drivers look at most is their personal best: the longest trip they have ever completed. It is there for the same reason a running app shows your furthest run — it is a number that only moves in one direction, which is a pleasant thing to see after a long shift.
A booking that was cancelled never happened, so it cannot be anybody's personal best however far it was going to go. Drivers are identified by number here, since the app already knows which driver it is showing the card to.
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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row per driver with at least one completed trip, with driver_id and the distance of their longest completed trip as longest_km, smallest driver_id first.
Example output
Shape only — these drivers and distances are invented. A whole-number distance comes back plain, as 9 rather than 9.0:
| driver_id | longest_km |
|---|---|
| 7 | 14.2 |
| 9 | 9 |
| 12 | 22.6 |
| 15 | 5.5 |
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