Zoomi city revenue scoreboard
Zoomi Rides runs in Dubai, Lahore, Singapore and Nairobi. The country managers want one scoreboard that shows how many trips each city actually completed and what they earned. Cancelled requests earn nothing and must not be counted.
Task: join trips to cities and, counting only trips with status 'Completed', return city (the city name), completed_trips, revenue_usd (the sum of fare_usd, ROUND(x, 2)) and avg_fare_usd (the average fare, ROUND(x, 2)). Sort by revenue_usd from high to low, then city A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | name TEXT | country TEXT |
|---|---|---|
| 1 | Dubai | UAE |
| 2 | Lahore | Pakistan |
| 3 | Singapore | Singapore |
| 4 | Nairobi | Kenya |
| id INTEGER | name TEXT | city_id INTEGER | joined_on TEXT | vehicle_type TEXT |
|---|---|---|---|---|
| 1 | Rashid Al Mansoori | 1 | 2025-09-18 | Economy |
| 2 | Tariq Mehmood | 2 | 2026-02-05 | Economy |
| 3 | Wei Jie Tan | 3 | 2025-09-08 | Comfort |
| 4 | Samuel Kiptoo | 4 | 2025-11-12 | Economy |
| 5 | Hassan Qureshi | 1 | 2025-12-20 | Economy |
| 6 | Priya Raman | 2 | 2025-06-24 | Economy |
Showing the first 6 of 8 rows.
| id INTEGER | name TEXT | home_city_id INTEGER | signup_date TEXT |
|---|---|---|---|
| 1 | Ayesha Khan | 1 | 2026-03-09 |
| 2 | Bilal Okafor | 2 | 2026-02-26 |
| 3 | Chen Silva | 3 | 2026-03-12 |
| 4 | Diego Tanaka | 4 | 2026-03-30 |
| 5 | Elena Weber | 1 | 2026-06-04 |
| 6 | Farid Nguyen | 2 | 2026-05-23 |
Showing the first 6 of 32 rows.
| id INTEGER | rider_id INTEGER | driver_id INTEGER | city_id INTEGER | requested_at TEXT | status TEXT | distance_km REAL | fare_usd REAL | surge REAL | rating INTEGER |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 5 | 1 | 2026-04-06 08:23:01 | Completed | 4 | 6.9 | 1 | 5 |
| 2 | 4 | 8 | 4 | 2026-04-06 10:42:25 | Completed | 13.1 | 9.44 | 1.25 | 5 |
| 3 | 3 | 3 | 3 | 2026-04-06 13:24:49 | Completed | 3.5 | 5.53 | 1 | 5 |
| 4 | 2 | 2 | 2 | 2026-04-06 17:17:07 | Completed | 15.8 | 9.5 | 1.5 | 4 |
| 5 | 1 | 1 | 1 | 2026-04-06 18:25:11 | Completed | 11.8 | 19.35 | 1.25 | 4 |
| 6 | 4 | 8 | 4 | 2026-04-06 21:01:43 | Completed | 16.6 | 9.3 | 1 | 4 |
Showing the first 6 of 548 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.