Peak-hour demand ranking in every city
Zoomi wants to text drivers a "go online now" nudge in each city's three hottest hours. Demand means every ride request in trips, whether it was later completed or cancelled, grouped by the hour of day of requested_at (0 to 23).
Task: for each city count requests per hour, rank the hours inside the city by request count (highest first) with RANK() so tied hours share a rank, and keep only ranks 1 to 3. Return city (name), request_hour (integer), requests and rnk. Sort by city A to Z, then rnk ascending, then request_hour ascending. Because of ties a city can have more than three rows.
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.