Interview Guides

Reels Cohort 7-Day Rolling Retention | Dataford Interview Questions - Dataford - Ace your Interview

Reels Cohort 7-Day Rolling Retention

Easy

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"ig_activity":[["1","104","2024-01-18","app_open","instagram_home"],["2","101","2024-01-09","app_open","instagram_home"],["3","102","2024-01-10","ig_save","ig_reels"],["4","103","2024-01-17","app_open","instagram_home"],["5","103","2024-01-12","like","ig_reels"],["6","106","2024-01-20","ig_save","ig_reels"],["7","111","2024-01-25","app_open","instagram_home"],["8","104","2024-01-16","share","fb_groups"],["9","101","2024-01-08","comment","ig_reels"],["10","102",null,"app_open","instagram_home"],

Output

[["2024-01-01","2","2","1.0000"],["2024-01-08","2","2","1.0000"],["2024-01-15","1","0","0.0000"],["null","1","0","0.0000"]]

Problem

Task

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

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

Sample Data

Example 1

Input

{"ig_activity":[["1","104","2024-01-18","app_open","instagram_home"],["2","101","2024-01-09","app_open","instagram_home"],["3","102","2024-01-10","ig_save","ig_reels"],["4","103","2024-01-17","app_open","instagram_home"],["5","103","2024-01-12","like","ig_reels"],["6","106","2024-01-20","ig_save","ig_reels"],["7","111","2024-01-25","app_open","instagram_home"],["8","104","2024-01-16","share","fb_groups"],["9","101","2024-01-08","comment","ig_reels"],["10","102",null,"app_open","instagram_home"],

Output

[["2024-01-01","2","2","1.0000"],["2024-01-08","2","2","1.0000"],["2024-01-15","1","0","0.0000"],["null","1","0","0.0000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Weekly Reels Engagement Drop DetectionHard

Compare Reels Signup Cohort EngagementHard

IG Reels Posting SpanHard

Next question

Reels Cohort 7-Day Rolling Retention

Easy

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

Task

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

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

Sample Data

Example 1

Input

{"ig_activity":[["1","104","2024-01-18","app_open","instagram_home"],["2","101","2024-01-09","app_open","instagram_home"],["3","102","2024-01-10","ig_save","ig_reels"],["4","103","2024-01-17","app_open","instagram_home"],["5","103","2024-01-12","like","ig_reels"],["6","106","2024-01-20","ig_save","ig_reels"],["7","111","2024-01-25","app_open","instagram_home"],["8","104","2024-01-16","share","fb_groups"],["9","101","2024-01-08","comment","ig_reels"],["10","102",null,"app_open","instagram_home"],

Output

[["2024-01-01","2","2","1.0000"],["2024-01-08","2","2","1.0000"],["2024-01-15","1","0","0.0000"],["null","1","0","0.0000"]]

Problem

Task

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

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

Sample Data

Example 1

Input

{"ig_activity":[["1","104","2024-01-18","app_open","instagram_home"],["2","101","2024-01-09","app_open","instagram_home"],["3","102","2024-01-10","ig_save","ig_reels"],["4","103","2024-01-17","app_open","instagram_home"],["5","103","2024-01-12","like","ig_reels"],["6","106","2024-01-20","ig_save","ig_reels"],["7","111","2024-01-25","app_open","instagram_home"],["8","104","2024-01-16","share","fb_groups"],["9","101","2024-01-08","comment","ig_reels"],["10","102",null,"app_open","instagram_home"],

Output

[["2024-01-01","2","2","1.0000"],["2024-01-08","2","2","1.0000"],["2024-01-15","1","0","0.0000"],["null","1","0","0.0000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Weekly Reels Engagement Drop DetectionHard

Compare Reels Signup Cohort EngagementHard

IG Reels Posting SpanHard

Next question