Reels Cohort 7-Day Rolling Retention

HardSQL · PostgreSQL

Problem

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.

Schema

reels_events

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

ig_activity

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

Tablesreels_eventsig_activity