The Long Hauls
Long trips break UrbanHop's pricing model. The per-kilometre rate was tuned on city-centre hops of three or four kilometres, and the airport runs at the top end lose money once the driver's return leg is accounted for. Pricing wants to look at the three longest bookings on record and work out what they should have cost.
They want the booking's id so they can pull the full record afterwards, and the distance so the list can be read at a glance. Cancelled bookings are included: a booking that was going to be a long trip is still evidence about how the rate behaves at that distance.
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 the id and distance_km of the three longest bookings, longest first.
Example output
Shape only — these bookings are invented. A whole-number distance comes back plain, as 28 rather than 28.0:
| id | distance_km |
|---|---|
| 47 | 31.4 |
| 22 | 28 |
| 35 | 19.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