Ranking Our Riders by Lifetime Spend
UrbanHop's loyalty programme is being redesigned around tiers rather than a single top-customer badge, so the product team needs the full board: every rider, what they have actually spent, and their position against everybody else.
"Actually spent" is the part with teeth. Three of the twelve rides were cancelled and nobody was charged for them, so those fares are not spend. Two riders took cancelled rides alongside their completed ones, and both would look better than they are if those fares were counted.
There is the usual split as well: a ride row carries the fare but only a rider id, and the rider's name lives in the other table.
riders — one row per rider. name is what appears on the board.
| 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 ride. rider_id points at a row in riders. fare is what the ride charged, in dollars. status is either completed or cancelled.
| 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 three columns, name, total_spent and spend_rank, with one row per rider who has at least one completed ride. total_spent is that rider's fares across their completed rides only, an exact dollar figure, not rounded. spend_rank is their position on the board, 1 for the highest total. Two riders on exactly the same total share a position and the position straight after it is skipped, so a board can read 1, 2, 2, 4. A rider whose every ride was cancelled does not appear at all. Rows come back sorted by spend_rank, best position first, and two riders sharing a position by name.
Example output — shape only, on an invented board; the two riders level on 64.5 show the shared position and the skip that follows it.
| name | total_spent | spend_rank |
|---|---|---|
| Ruth Abara | 120 | 1 |
| Dana Reyes | 64.5 | 2 |
| Ollie Kane | 64.5 | 2 |
| Kofi Mensah | 22 | 4 |
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