Write a query to compute user retention cohorts over successive quarters using conditional aggregation and window frames.
Use each user's signup quarter as the cohort. Count users active in each successive quarter, include quarters with zero retained users through the latest observed activity quarter, and calculate retention percentages and cumulative retained users.
cohort_quarter, quarter_number, cohort_users, retained_users, retention_rate, and cumulative_retained_userscohort_quarter, then quarter_number| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| signup_channel | VARCHAR(30) | Channel attributed to the signup |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity event identifier |
| user_id | INT | User associated with the activity |
| activity_date | DATE | Date of the activity event |
| event_type | VARCHAR(40) | Type of activity recorded |