Which City Generates the Most Ride Revenue?
UrbanHop has a new driver-recruitment budget and can only afford to spend it in one city. The regional lead wants the city that has brought in the most fare money, and one thing is non-negotiable: a cancelled ride earns nothing, so its fare must not be counted. Three of the twelve rides were cancelled, and their fares are large enough to move the answer if they slip in.
There is a second wrinkle. A ride row has no city on it. Cities belong to riders, so a ride's city is the home city of the person who took it — and riders and rides are kept in separate tables.
riders — one row per rider. city is the rider's home city.
| 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 exactly one row with two columns, city and total_revenue — the single rider city with the largest total fare across its completed rides, and that total. Cancelled rides contribute nothing. total_revenue is an exact dollar figure and is not rounded. If two cities were tied at the top only one row would still come back, and which of them it is would be unspecified.
Example output — shape only, on an invented city and total.
| city | total_revenue |
|---|---|
| Boise | 214.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