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.
signup_date falls between 2024-01-01 and 2024-01-31.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 |
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 |
| cohort_month | cohort_size | retained_riders | retention_rate_pct |
|---|---|---|---|
| 2024-01 | 6 | 3 | 50.00 |
| Column | Type | Description |
|---|---|---|
| rider_idPK | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider full name |
| signup_date | DATE | Date the rider signed up for Uber |
| city | VARCHAR(50) | Primary signup city |
| Column | Type | Description |
|---|---|---|
| trip_idPK | 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 such as completed or canceled |
| fare_usd | NUMERIC(8,2) | Final trip fare in USD |
{"trips":[["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"],["103","2","2024-01-15","completed","22.00"],["111","7","2024-02-08","completed","13.75"],["108","5","2024-02-14","canceled","0.00"],["110",null,"2024-02-12","completed","9.90"],["106","3","2024-03-01","completed","27.30"],["102","1","2024-02-18","completed","24.00"],["112","10","2024-02-22","completed","19.8Output[["2024-01","11","4","36.36"]]