At SwiftRide, the analytics team wants to measure how ride activity changes over time for different user signup cohorts. Write a SQL query to calculate monthly ride frequency by cohort.
signup_date.status = 'completed'.country is 'US' and whose account status is 'active' or 'paused'.cohort_month, then ride_month.Use the tables below.
users
| column | type | description |
|---|---|---|
| user_id | INT | Primary key |
| signup_date | DATE | Date the user created an account |
| country | VARCHAR(50) | User country |
| account_status | VARCHAR(20) | Current account status |
| referral_source_id | INT | Optional referral source |
rides
| column | type | description |
|---|---|---|
| ride_id | INT | Primary key |
| user_id | INT | Rider identifier |
| ride_date | DATE | Date of the ride |
| status | VARCHAR(20) | Ride status |
| fare_amount | NUMERIC(10,2) | Fare charged for the ride |
referral_sources
| column | type | description |
|---|---|---|
| referral_source_id | INT | Primary key |
| source_name | VARCHAR(50) | Marketing or referral source |
users
| user_id | signup_date | country | account_status | referral_source_id |
|---|---|---|---|---|
| 3 | 2024-02-05 | US | paused | 2 |
| 1 | 2024-01-10 | US | active | 1 |
| 8 | 2024-03-20 | US | active | 4 |
| 5 | 2024-02-18 | CA | active | 2 |
| 2 | 2024-01-22 | US | active | NULL |
| 7 | 2024-03-01 | US | banned | 3 |
| 4 | 2024-02-27 | US | active | 3 |
| 6 | 2024-03-03 | US | active | NULL |
| 9 | 2024-01-31 | US | paused | 99 |
| 10 | 2024-04-02 | US | active | 1 |
rides
| ride_id | user_id | ride_date | status | fare_amount |
|---|---|---|---|---|
| 101 | 1 | 2024-01-15 | completed | 18.50 |
| 102 | 1 | 2024-02-03 | completed | 12.00 |
| 103 | 1 | 2024-02-18 | cancelled | 0.00 |
| 104 | 2 | 2024-02-10 | completed | 9.75 |
| 105 | 2 | 2024-03-01 | completed | 14.20 |
| 106 | 3 | 2024-02-20 | completed | 7.40 |
| 107 | 3 | 2024-03-05 | completed | 11.10 |
| 108 | 4 | 2024-03-12 | completed | 16.00 |
| 109 | 4 | 2024-03-28 | completed | 8.50 |
| 110 | 5 | 2024-03-15 | completed | 13.00 |
| 111 | 6 | 2024-03-18 | completed | 10.00 |
| 112 | 6 | 2024-04-01 | completed | 15.00 |
| 113 | 7 | 2024-03-22 | completed | 20.00 |
| 114 | 8 | 2024-04-05 | cancelled | 0.00 |
| 115 | 9 | 2024-02-14 | completed | 6.25 |
| 116 | 9 | 2024-02-21 | completed | 5.75 |
| 117 | 10 | 2024-04-10 | completed | 19.00 |
| 118 | 2 | 2024-03-15 | completed | 8.80 |
| cohort_month | ride_month | active_riders | completed_rides | avg_rides_per_active_rider |
|---|---|---|---|---|
| 2024-01 | 2024-01 | 1 | 1 | 1.00 |
| 2024-01 | 2024-02 | 4 | 5 | 1.25 |
| 2024-01 | 2024-03 | 2 | 3 | 1.50 |
| 2024-02 | 2024-02 | 1 | 1 | 1.00 |
| 2024-02 | 2024-03 | 2 | 3 | 1.50 |
| 2024-03 | 2024-03 | 1 | 1 | 1.00 |
| 2024-03 | 2024-04 | 1 | 1 | 1.00 |
| 2024-04 | 2024-04 | 1 | 1 | 1.00 |