Task
Uber wants to measure early rider retention for users who signed up in January 2024. Write a SQL query to calculate the retention rate for that signup cohort.
Define a retained rider as a rider who signed up in January 2024 and completed at least one trip in February 2024.
Requirements
- Identify all riders whose
signup_date falls between 2024-01-01 and 2024-01-31.
- Count how many riders are in that January 2024 signup cohort.
- Count how many of those riders completed at least one trip in February 2024.
- Return the cohort month, cohort size, retained riders, and retention rate as a percentage rounded to 2 decimals.
Table Definitions
riders
| column | type | description |
|---|
| rider_id | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider name |
| signup_date | DATE | Date the rider created an Uber account |
| city | VARCHAR(50) | Primary signup city |
trips
| column | type | description |
|---|
| trip_id | INT | Unique trip identifier |
| rider_id | INT | Rider who requested the trip |
| trip_date | DATE | Date of the trip |
| trip_status | VARCHAR(20) | Trip outcome |
| fare_usd | NUMERIC(8,2) | Final fare in USD |
Sample Data
riders
| rider_id | rider_name | signup_date | city |
|---|
| 8 | Henry Park | 2024-02-01 | Miami |
| 2 | Brian Lee | 2024-01-10 | San Francisco |
| 5 | Eva Chen | 2024-01-31 | Seattle |
| 1 | Alice Kim | 2024-01-03 | New York |
trips
| trip_id | rider_id | trip_date | trip_status | fare_usd |
|---|
| 104 | 2 | 2024-02-20 | completed | 31.00 |
| 101 | 1 | 2024-02-03 | completed | 18.50 |
| 107 | 4 | 2024-02-28 | completed | 16.20 |
| 109 | 6 | 2024-02-10 | completed | 11.40 |
Expected Output
| cohort_month | cohort_size | retained_riders | retention_rate_pct |
|---|
| 2024-01 | 6 | 3 | 50.00 |
