Price per Kilometer
Pricing at UrbanHop is hunting for outliers: bookings whose effective rate per kilometre is far above what the fare table should produce. A high rate usually means a short trip, since the base fare is spread over very little distance, but occasionally it means a pricing bug, and those are worth finding while they are still cheap.
Only completed bookings are in scope — a cancelled one was never charged, so its rate is hypothetical. Pricing looks at the five worst offenders each morning and wants the booking id so they can pull the full record.
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 id and the fare per kilometre rounded to two decimal places as fare_per_km, for completed bookings only, keeping just the five highest rates. Highest rate first; bookings on the same rate are separated by id, smallest first.
Example output
Shape only — these bookings and rates are invented:
| id | fare_per_km |
|---|---|
| 47 | 6.4 |
| 22 | 5.85 |
| 35 | 5.2 |
| 19 | 4.75 |
| 26 | 4.1 |
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