What does surge pricing really do?
Regulators in several cities ask Zoomi to prove that surge pricing does not just push riders to give up. A request is surged when surge > 1 and normal when surge <= 1. A cancelled request is any trip whose status is not 'Completed'. The fare per kilometre uses completed trips only: the sum of fare_usd divided by the sum of distance_km for that group.
Task: per city (name) return normal_cancel_pct and surge_cancel_pct (cancelled requests as a percent of all requests in that group), normal_fare_per_km and surge_fare_per_km, and surge_premium_pct = 100 * (surge fare per km / normal fare per km - 1), computed from the unrounded values. Round every one of the five measures with ROUND(x, 2) and name the first column city. Sort by 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.