Task
RideNow wants to measure early rider retention for new NYC riders. Write a SQL query to calculate the 7-day rolling retention rate by signup date cohort.
A rider is considered retained if they complete at least one trip in NYC between day 1 and day 7 after signup, inclusive. The signup day itself does not count.
Requirements
- Consider only riders whose
signup_city is 'NYC'.
- Build daily signup cohorts using
signup_date from the riders table.
- For each cohort date, calculate:
- total new riders
- retained riders
- retention rate = retained riders / total new riders
- Return one row per
signup_date, ordered by signup_date ascending.
- Ignore trips outside NYC when determining retention.
- Count each rider at most once in the retained population, even if they took multiple qualifying trips.
Table Definitions
riders
| column | type | description |
|---|
| rider_id | INT | Unique rider identifier |
| signup_date | DATE | Rider signup date |
| signup_city | VARCHAR(50) | City where the rider signed up |
| referral_code | VARCHAR(20) | Optional referral code |
| | |
trips
| column | type | description |
|---|
| trip_id | INT | Unique trip identifier |
| rider_id | INT | Rider who took the trip |
| trip_date | DATE | Trip completion date |
| city | VARCHAR(50) | City where the trip occurred |
| fare_amount | NUMERIC(10,2) | Trip fare |
Sample Data
riders
| rider_id | signup_date | signup_city | referral_code |
|---|
| 104 | 2024-01-03 | NYC | REF20 |
| 101 | 2024-01-01 | NYC | REF10 |
| 109 | 2024-01-05 | NYC | |
| 103 | 2024-01-02 | NYC | NULL |
| 108 | 2024-01-04 | NYC | REF10 |
| 106 | 2024-01-03 | Boston | REF30 |
| 102 | 2024-01-01 | NYC | NULL |
| 110 | 2024-01-05 | NYC | REF50 |
| 105 | 2024-01-03 | NYC | REF20 |
| 107 | 2024-01-04 | NYC | NULL |
trips
| trip_id | rider_id | trip_date | city | fare_amount |
|---|
| 205 | 103 | 2024-01-09 | NYC | 17.00 |
| 201 | 101 | 2024-01-02 | NYC | 15.50 |
| 210 | 106 | 2024-01-05 | Boston | 9.00 |
| 203 | 102 | 2024-01-08 | NYC | 22.00 |
| 208 | 105 | 2024-01-10 | NYC | 11.00 |
| 212 | 108 | 2024-01-12 | NYC | 13.00 |
| 206 | 104 | 2024-01-04 | NYC | 8.00 |
| 211 | 107 | 2024-01-05 | NYC | 16.00 |
| 202 | 101 | 2024-01-05 | NYC | 12.00 |
| 204 | 103 | 2024-01-03 | Chicago | 18.00 |
| 207 | 104 | 2024-01-11 | NYC | 14.00 |
| 209 | 105 | 2024-01-05 | NYC | 10.00 |
Expected Output
| signup_date | new_riders | retained_riders | retention_rate |
|---|
| 2024-01-01 | 2 | 2 | 1.0000 |
| 2024-01-02 | 1 | 0 | 0.0000 |
| 2024-01-03 | 2 | 2 | 1.0000 |
| 2024-01-04 | 2 | 1 | 0.5000 |
| 2024-01-05 | 2 | 0 | 0.0000 |
