Task
StreamWave wants to compare how different signup cohorts behave after registration. Write a SQL query to measure 30-day engagement by monthly signup cohort.
Requirements
- Define each user's cohort as the month of their
signup_date.
- For each cohort month, calculate:
- total users in the cohort
- users with at least one
session_start event within 30 days of signup
- the percentage of engaged users in the cohort
- Return one row per cohort month.
- Order results by cohort month ascending.
Table Definitions
users
| Column | Type | Description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user registered |
| acquisition_channel | VARCHAR(50) | Marketing channel that acquired the user |
events
| Column | Type | Description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_type | VARCHAR(50) | Event name |
| event_date | DATE | Date of the event |
Sample Data
users
| user_id | signup_date | acquisition_channel |
|---|
| 3 | 2024-02-20 | Organic |
| 1 | 2024-01-05 | Paid Search |
| 8 | 2024-03-28 | Referral |
| 5 | 2024-02-25 | Email |
events
| event_id | user_id | event_type | event_date |
|---|
| 104 | 2 | purchase | 2024-02-10 |
| 101 | 1 | session_start | 2024-01-06 |
| 110 | 5 | session_start | 2024-03-30 |
| 116 | 10 | session_start | 2024-03-10 |
Expected Output
| cohort_month | total_users | engaged_users_30d | engagement_rate_30d |
|---|
| 2024-01 | 4 | 3 | 75.00 |
| 2024-02 | 3 | 2 | 66.67 |
| 2024-03 | 3 | 1 | 33.33 |
