Rider cohorts by month of first trip
The growth team groups riders into cohorts by the month of their first ride request (the earliest requested_at of any status). Riders who never requested a ride belong to no cohort. They want to see whether early cohorts become heavier users than recent ones, so the cohorts need volume and a heavy-user count. A rider is heavy when they made 10 or more requests in total in the data.
Task: use CTEs to return one row per cohort with cohort_month (YYYY-MM), riders (riders in that cohort), total_trips (all requests of those riders, any status), trips_per_rider (ROUND(x, 2)) and heavy_riders. Sort by cohort_month ascending.
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.