Task
Meta’s Instagram Reels team wants a cohort view of retention. Write a SQL query to calculate the 7-day rolling retention rate for Instagram users, segmented by the calendar week of their first Reels use.
Rolling retention here means: for each user, check whether they had any Instagram activity on or after day 7 from their first Reels use date. Then compute the share of retained users within each first-Reels week cohort.
Requirements
- Identify each user’s first Reels use date from
reels_events.
- Bucket users into cohorts using
DATE_TRUNC('week', first_reels_date).
- A user is retained if they have at least one row in
ig_activity where activity_date >= first_reels_date + INTERVAL '7 days'.
- Return, for each cohort week:
- cohort week
- cohort size
- retained users
- 7-day rolling retention rate
- Exclude rows where
event_type is not a real Reels use event.
Table Definitions
reels_events
| column | type | description |
|---|
| event_id | INT | Primary key for the Reels event |
| user_id | INT | Instagram user id |
| event_date | DATE | Date of the Reels event |
| event_type | VARCHAR(50) | Reels event name |
| surface | VARCHAR(50) | Meta surface where the event happened |
| | |
ig_activity
| column | type | description |
|---|
| activity_id | INT | Primary key for the activity event |
| user_id | INT | Instagram user id |
| activity_date | DATE | Date of Instagram activity |
| activity_type | VARCHAR(50) | Activity type such as open, like, ig_save |
| surface | VARCHAR(50) | Product surface for the activity |
Sample Data
reels_events
| event_id | user_id | event_date | event_type | surface |
|---|
| 1 | 101 | 2024-01-03 | reels_play | ig_reels |
| 2 | 102 | 2024-01-04 | reels_play | ig_reels |
| 3 | 103 | 2024-01-10 | reels_play | ig_reels |
| 4 | 104 | 2024-01-11 | reels_play | ig_reels |
| 5 | 105 | 2024-01-12 | reels_impression | ig_reels |
| 6 | 106 | 2024-01-15 | reels_play | ig_reels |
ig_activity
| activity_id | user_id | activity_date | activity_type | surface |
|---|
| 1 | 101 | 2024-01-10 | app_open | instagram_home |
| 2 | 102 | 2024-01-08 | ig_save | ig_reels |
| 3 | 103 | 2024-01-17 | app_open | instagram_home |
| 4 | 104 | 2024-01-16 | like | fb_groups |
| 5 | 105 | 2024-01-20 | app_open | instagram_home |
| 6 | 106 | 2024-01-21 | ig_save | ig_reels |
Expected Output
| cohort_week | cohort_size | retained_users | rolling_retention_7d |
|---|
| 2024-01-01 | 2 | 1 | 0.5000 |
| 2024-01-08 | 3 | 3 | 1.0000 |
| 2024-01-15 | 1 | 0 | 0.0000 |
