StreamWave wants to analyze how user behavior changes over time. Write a PostgreSQL query to summarize monthly user activity for each signup cohort month.
Your query should focus on users who signed up in January or February 2024 and measure their behavior during the first 3 calendar months starting from their signup month.
signup_month and activity_month.engagement_status column:
high if average events per active user is at least 2low otherwisesignup_month, then activity_month.users| column | type | description |
|---|---|---|
| user_id | INT | Primary key for each user |
| signup_date | DATE | Date the user registered |
| acquisition_channel | VARCHAR(50) | Marketing channel |
events| column | type | description |
|---|---|---|
| event_id | INT | Primary key for each event |
| user_id | INT | User who generated the event |
| event_date | DATE | Event date |
| event_type | VARCHAR(50) | Type of action |
| device_type | VARCHAR(20) | Device used |
subscriptions| column | type | description |
|---|---|---|
| subscription_id | INT | Subscription record |
| user_id | INT | User tied to the subscription |
| plan_name | VARCHAR(30) | Subscription plan |
| start_date | DATE | Subscription start date |
| end_date | DATE | Subscription end date, if canceled |
users| user_id | signup_date | acquisition_channel |
|---|---|---|
| 3 | 2024-02-10 | paid_search |
| 1 | 2024-01-05 | organic |
| 8 | 2024-03-01 | referral |
| 2 | 2024-01-20 | referral |
events| event_id | user_id | event_date | event_type | device_type |
|---|---|---|---|---|
| 104 | 2 | 2024-02-02 | play | web |
| 101 | 1 | 2024-01-06 | login | mobile |
| 110 | 4 | 2024-04-02 | play | mobile |
| 112 | 6 | 2024-03-16 | login | web |
subscriptions| subscription_id | user_id | plan_name | start_date | end_date |
|---|---|---|---|---|
| 201 | 1 | basic | 2024-01-05 | null |
| 206 | 6 | basic | 2024-03-15 | null |
| 203 | 3 | premium | 2024-02-10 | 2024-03-31 |
| 208 | 9 | basic | 2024-02-28 | null |
| signup_month | activity_month | cohort_users | active_users | total_events | engagement_status |
|---|---|---|---|---|---|
| 2024-01 | 2024-01 | 3 | 2 | 3 | low |
| 2024-01 | 2024-02 | 3 | 2 | 3 | low |
| 2024-01 | 2024-03 | 3 | 1 | 1 | low |
| 2024-02 | 2024-02 | 4 | 2 | 3 | low |
| 2024-02 | 2024-03 | 4 | 2 | 4 | high |
| 2024-02 | 2024-04 | 4 | 1 | 1 | low |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user registered |
| acquisition_channel | VARCHAR(50) | Marketing acquisition source |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_date | DATE | Date of the event |
| event_type | VARCHAR(50) | Type of user action |
| device_type | VARCHAR(20) | Device used for the event |
| Column | Type | Description |
|---|---|---|
| subscription_idPK | INT | Unique subscription identifier |
| user_id | INT | User tied to the subscription |
| plan_name | VARCHAR(30) | Subscription plan name |
| start_date | DATE | Subscription start date |
| end_date | DATE | Subscription end date if canceled |
{"users":[["3","2024-02-10","paid_search"],["1","2024-01-05","organic"],["8","2024-03-01","referral"],["2","2024-01-20","referral"],["5","2024-02-28",null],["4","2024-02-25","organic"],["7","2023-12-31","paid_social"],["6","2024-03-15","paid_search"],["9","2024-02-28","affiliate"],["10","2024-01-31","organic"],["11","2024-02-01","partner"],["12","2024-02-18","sms"],["13","2024-02-19","seo"],["14","2024-03-17","event"],["15","2024-03-13","null"],["16","2024-02-25","retargeting"],["17","2024-01-13Output[["2024-01","2024-01","7","2","3","low"],["2024-01","2024-02","7","3","5","low"],["2024-01","2024-03","7","3","5","low"],["2024-02","2024-02","12","2","3","low"],["2024-02","2024-03","12","4","6","low"],["2024-02","2024-04","12","1","1","low"]]
