Task
You are analyzing early engagement for two Instagram Reels signup cohorts in the AARRR funnel. Write a PostgreSQL query to compare users who signed up in January 2024 versus February 2024. For each cohort, return the cohort size, the number of users who had at least one Reels engagement event in their first 14 days after signup, total 14-day Reels engagements, average 14-day engagements per user, and the lift in average engagements versus the previous cohort.
Count only view, like, comment, and save events on the reels surface. A user with no qualifying events should still remain in the cohort denominator.
Schema
users
| column | type | description |
|---|
| user_id | INT | User identifier |
| signup_date | DATE | Account signup date |
| signup_surface | VARCHAR(50) | Surface where signup happened |
| country_code | VARCHAR(10) | User country |
engagement_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| user_id | INT | User who generated the event |
| event_date | DATE | Event date |
| surface | VARCHAR(50) | Meta surface, such as reels or fb_groups |
| event_type | VARCHAR(50) | Engagement action |
| event_count | INT | Number of actions represented by the row |
Sample data
users
| user_id | signup_date | signup_surface | country_code |
|---|
| 101 | 2024-01-03 | ig_reels | US |
| 102 | 2024-01-10 | facebook | IN |
| 105 | 2024-02-02 | ig_reels | BR |
engagement_events
| event_id | user_id | event_date | surface | event_type | event_count |
|---|
| 1 | 101 | 2024-01-04 | reels | view | 5 |
| 2 | 101 | 2024-01-08 | reels | save | 1 |
| 7 | 105 | 2024-02-03 | reels | view | 4 |
Expected output
| cohort_month | cohort_size | engaged_users_14d | total_engagements_14d | avg_engagements_per_user_14d | prev_cohort_avg | avg_engagement_lift_vs_prev |
|---|
| 2024-01 | 4 | 3 | 13 | 3.25 | null | null |
| 2024-02 | 4 | 3 | 17 | 4.25 | 3.25 | 1.00 |
