Your question is Reels Cohort 7-Day Rolling Retention. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
reels_events.DATE_TRUNC('week', first_reels_date).ig_activity where activity_date >= first_reels_date + INTERVAL '7 days'.event_type is not a real Reels use event.| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Primary key for each Reels event |
| user_id | INT | Instagram user identifier |
| event_date | DATE | Date the Reels event occurred |
| event_type | VARCHAR(50) | Type of Reels event such as reels_play or reels_impression |
| surface | VARCHAR(50) | Meta surface where the event was logged |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Primary key for each Instagram activity event |
| user_id | INT | Instagram user identifier |
| activity_date | DATE | Date of user activity |
| activity_type | VARCHAR(50) | Instagram activity type such as app_open, like, or ig_save |
| surface | VARCHAR(50) | Surface where the activity occurred |