Task
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.
Requirements
- Return one row per
signup_month and activity_month.
- Count:
- total users in the cohort
- active users in that month (users with at least one event)
- total events in that month
- Add an
engagement_status column:
high if average events per active user is at least 2low otherwise
- Order results by
signup_month, then activity_month.
Table Definitions
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 |
Sample Data
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 |
Expected Output
| 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 |
