Premium Trips
UrbanHop's pricing analysts want a sample of the platform's higher-value trips — the completed bookings that brought in more than a typical completed booking does. There is no fixed threshold for that, and deliberately so: the platform's typical fare moves with the mix of cities and trip lengths, and a number written down last quarter would be measuring the wrong thing by now.
So the bar is whatever the current typical completed fare turns out to be, computed from the same completed bookings the sample is drawn from. A booking sitting exactly on the bar has not beaten it.
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 fare for every completed booking whose fare is strictly above the mean fare of all completed bookings, highest fare first.
Example output
Shape only — these bookings and fares are invented:
| id | fare |
|---|---|
| 47 | 44.75 |
| 22 | 39 |
| 35 | 31.5 |
| 19 | 28.25 |
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