Interview Guides

Weekly Reels Engagement Drop Detection | Dataford Interview Questions - Dataford - Ace your Interview

Weekly Reels Engagement Drop Detection

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Meta’s Instagram Reels team wants to flag users whose weekly engagement fell sharply, which can be useful when monitoring AARRR engagement trends or diagnosing a novelty effect after a product change. Write a SQL query to find users whose total engagement dropped by more than 50% week over week.

Requirements

Treat engagement_count as the weekly engagement signal for each user.
Aggregate engagement by user and calendar week using DATE_TRUNC('week', event_date).
Compare each user’s weekly total to their immediately previous active week.
Return only users where the current week is less than 50% of the previous week.
Exclude rows where the previous week’s engagement is NULL or 0.
Return: user_id, week_start, current_week_engagement, previous_week_engagement, and drop_pct.
Order results by drop_pct descending, then user_id, then week_start.

Table Definitions

`reels_engagement_events`

Column	Type	Description
event_id	INT	Primary key for the event row
user_id	INT	Instagram user identifier
event_date	DATE	Date of the engagement event
surface	VARCHAR(50)	Meta surface where the event happened
engagement_type	VARCHAR(50)	Type of engagement action
engagement_count	INT	Count contributed by the row

Sample Data

`reels_engagement_events`

event_id	user_id	event_date	surface	engagement_type	engagement_count
7	103	2024-05-14	ig_reels	like	10
1	101	2024-05-07	ig_reels	like	40
10	104	2024-05-06	ig_reels	like	0
3	101	2024-05-14	ig_reels	like	15
12	105	2024-05-13	fb_groups	comment	40
5	102	2024-05-07	ig_reels	ig_save	20
9	103	2024-05-21	ig_reels	comment	5
2	101	2024-05-08	ig_reels	ig_save	20
11	104	2024-05-13	ig_reels	like	10
4	101	2024-05-15	ig_reels	comment	10
8	103	2024-05-20	ig_reels	like	5
6	102	2024-05-14	ig_reels	ig_save	8

Expected Output

user_id	week_start	current_week_engagement	previous_week_engagement	drop_pct
102	2024-05-13	8	20	60.00
101	2024-05-13	25	60	58.33

Schema

reels_engagement_events

Column	Type	Description
event_idPK	INT	Primary key for the engagement event row
user_id	INT	Instagram user identifier
event_date	DATE	Date the engagement happened
surface	VARCHAR(50)	Meta product surface such as ig_reels or fb_groups
engagement_type	VARCHAR(50)	Type of engagement action such as like, comment, or ig_save
engagement_count	INT	Count contributed by the event row

Sample Data

Example 1

Input

{"reels_engagement_events":[[7,103,"2024-05-14","ig_reels","like",10],[1,101,"2024-05-07","ig_reels","like",40],[10,104,"2024-05-06","ig_reels","like",0],[3,101,"2024-05-14","ig_reels","like",15],[12,105,"2024-05-13","fb_groups","comment",40],[5,102,"2024-05-07","ig_reels","ig_save",20],[9,103,"2024-05-21","ig_reels","comment",5],[2,101,"2024-05-08","ig_reels","ig_save",20],[11,104,"2024-05-13","ig_reels","like",10],[4,101,"2024-05-15","ig_reels","comment",10],[8,103,"2024-05-20","ig_reels","lik

Output[["102","2024-05-13","8","20","60.00"],["101","2024-05-13","25","60","58.33"]]

Problem

Task

Requirements

Treat engagement_count as the weekly engagement signal for each user.
Aggregate engagement by user and calendar week using DATE_TRUNC('week', event_date).
Compare each user’s weekly total to their immediately previous active week.
Return only users where the current week is less than 50% of the previous week.
Exclude rows where the previous week’s engagement is NULL or 0.
Return: user_id, week_start, current_week_engagement, previous_week_engagement, and drop_pct.
Order results by drop_pct descending, then user_id, then week_start.

Table Definitions

`reels_engagement_events`

Column	Type	Description
event_id	INT	Primary key for the event row
user_id	INT	Instagram user identifier
event_date	DATE	Date of the engagement event
surface	VARCHAR(50)	Meta surface where the event happened
engagement_type	VARCHAR(50)	Type of engagement action
engagement_count	INT	Count contributed by the row

Sample Data

`reels_engagement_events`

event_id	user_id	event_date	surface	engagement_type	engagement_count
7	103	2024-05-14	ig_reels	like	10
1	101	2024-05-07	ig_reels	like	40
10	104	2024-05-06	ig_reels	like	0
3	101	2024-05-14	ig_reels	like	15
12	105	2024-05-13	fb_groups	comment	40
5	102	2024-05-07	ig_reels	ig_save	20
9	103	2024-05-21	ig_reels	comment	5
2	101	2024-05-08	ig_reels	ig_save	20
11	104	2024-05-13	ig_reels	like	10
4	101	2024-05-15	ig_reels	comment	10
8	103	2024-05-20	ig_reels	like	5
6	102	2024-05-14	ig_reels	ig_save	8

Expected Output

user_id	week_start	current_week_engagement	previous_week_engagement	drop_pct
102	2024-05-13	8	20	60.00
101	2024-05-13	25	60	58.33

Schema

reels_engagement_events

Column	Type	Description
event_idPK	INT	Primary key for the engagement event row
user_id	INT	Instagram user identifier
event_date	DATE	Date the engagement happened
surface	VARCHAR(50)	Meta product surface such as ig_reels or fb_groups
engagement_type	VARCHAR(50)	Type of engagement action such as like, comment, or ig_save
engagement_count	INT	Count contributed by the event row

Sample Data

Example 1

Input

{"reels_engagement_events":[[7,103,"2024-05-14","ig_reels","like",10],[1,101,"2024-05-07","ig_reels","like",40],[10,104,"2024-05-06","ig_reels","like",0],[3,101,"2024-05-14","ig_reels","like",15],[12,105,"2024-05-13","fb_groups","comment",40],[5,102,"2024-05-07","ig_reels","ig_save",20],[9,103,"2024-05-21","ig_reels","comment",5],[2,101,"2024-05-08","ig_reels","ig_save",20],[11,104,"2024-05-13","ig_reels","like",10],[4,101,"2024-05-15","ig_reels","comment",10],[8,103,"2024-05-20","ig_reels","lik

Output[["102","2024-05-13","8","20","60.00"],["101","2024-05-13","25","60","58.33"]]

Practice SQL

PostgreSQL

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

Up next

Reels Cohort 7-Day Rolling RetentionEasy

Compare Reels Signup Cohort EngagementHard

IG Reels Posting SpanHard

Next question

Weekly Reels Engagement Drop Detection

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Requirements

Treat engagement_count as the weekly engagement signal for each user.
Aggregate engagement by user and calendar week using DATE_TRUNC('week', event_date).
Compare each user’s weekly total to their immediately previous active week.
Return only users where the current week is less than 50% of the previous week.
Exclude rows where the previous week’s engagement is NULL or 0.
Return: user_id, week_start, current_week_engagement, previous_week_engagement, and drop_pct.
Order results by drop_pct descending, then user_id, then week_start.

Table Definitions

`reels_engagement_events`

Column	Type	Description
event_id	INT	Primary key for the event row
user_id	INT	Instagram user identifier
event_date	DATE	Date of the engagement event
surface	VARCHAR(50)	Meta surface where the event happened
engagement_type	VARCHAR(50)	Type of engagement action
engagement_count	INT	Count contributed by the row

Sample Data

`reels_engagement_events`

event_id	user_id	event_date	surface	engagement_type	engagement_count
7	103	2024-05-14	ig_reels	like	10
1	101	2024-05-07	ig_reels	like	40
10	104	2024-05-06	ig_reels	like	0
3	101	2024-05-14	ig_reels	like	15
12	105	2024-05-13	fb_groups	comment	40
5	102	2024-05-07	ig_reels	ig_save	20
9	103	2024-05-21	ig_reels	comment	5
2	101	2024-05-08	ig_reels	ig_save	20
11	104	2024-05-13	ig_reels	like	10
4	101	2024-05-15	ig_reels	comment	10
8	103	2024-05-20	ig_reels	like	5
6	102	2024-05-14	ig_reels	ig_save	8

Expected Output

user_id	week_start	current_week_engagement	previous_week_engagement	drop_pct
102	2024-05-13	8	20	60.00
101	2024-05-13	25	60	58.33

Schema

reels_engagement_events

Column	Type	Description
event_idPK	INT	Primary key for the engagement event row
user_id	INT	Instagram user identifier
event_date	DATE	Date the engagement happened
surface	VARCHAR(50)	Meta product surface such as ig_reels or fb_groups
engagement_type	VARCHAR(50)	Type of engagement action such as like, comment, or ig_save
engagement_count	INT	Count contributed by the event row

Sample Data

Example 1

Input

{"reels_engagement_events":[[7,103,"2024-05-14","ig_reels","like",10],[1,101,"2024-05-07","ig_reels","like",40],[10,104,"2024-05-06","ig_reels","like",0],[3,101,"2024-05-14","ig_reels","like",15],[12,105,"2024-05-13","fb_groups","comment",40],[5,102,"2024-05-07","ig_reels","ig_save",20],[9,103,"2024-05-21","ig_reels","comment",5],[2,101,"2024-05-08","ig_reels","ig_save",20],[11,104,"2024-05-13","ig_reels","like",10],[4,101,"2024-05-15","ig_reels","comment",10],[8,103,"2024-05-20","ig_reels","lik

Output[["102","2024-05-13","8","20","60.00"],["101","2024-05-13","25","60","58.33"]]

Problem

Task

Requirements

Treat engagement_count as the weekly engagement signal for each user.
Aggregate engagement by user and calendar week using DATE_TRUNC('week', event_date).
Compare each user’s weekly total to their immediately previous active week.
Return only users where the current week is less than 50% of the previous week.
Exclude rows where the previous week’s engagement is NULL or 0.
Return: user_id, week_start, current_week_engagement, previous_week_engagement, and drop_pct.
Order results by drop_pct descending, then user_id, then week_start.

Table Definitions

`reels_engagement_events`

Column	Type	Description
event_id	INT	Primary key for the event row
user_id	INT	Instagram user identifier
event_date	DATE	Date of the engagement event
surface	VARCHAR(50)	Meta surface where the event happened
engagement_type	VARCHAR(50)	Type of engagement action
engagement_count	INT	Count contributed by the row

Sample Data

`reels_engagement_events`

event_id	user_id	event_date	surface	engagement_type	engagement_count
7	103	2024-05-14	ig_reels	like	10
1	101	2024-05-07	ig_reels	like	40
10	104	2024-05-06	ig_reels	like	0
3	101	2024-05-14	ig_reels	like	15
12	105	2024-05-13	fb_groups	comment	40
5	102	2024-05-07	ig_reels	ig_save	20
9	103	2024-05-21	ig_reels	comment	5
2	101	2024-05-08	ig_reels	ig_save	20
11	104	2024-05-13	ig_reels	like	10
4	101	2024-05-15	ig_reels	comment	10
8	103	2024-05-20	ig_reels	like	5
6	102	2024-05-14	ig_reels	ig_save	8

Expected Output

user_id	week_start	current_week_engagement	previous_week_engagement	drop_pct
102	2024-05-13	8	20	60.00
101	2024-05-13	25	60	58.33

Schema

reels_engagement_events

Column	Type	Description
event_idPK	INT	Primary key for the engagement event row
user_id	INT	Instagram user identifier
event_date	DATE	Date the engagement happened
surface	VARCHAR(50)	Meta product surface such as ig_reels or fb_groups
engagement_type	VARCHAR(50)	Type of engagement action such as like, comment, or ig_save
engagement_count	INT	Count contributed by the event row

Sample Data

Example 1

Input

{"reels_engagement_events":[[7,103,"2024-05-14","ig_reels","like",10],[1,101,"2024-05-07","ig_reels","like",40],[10,104,"2024-05-06","ig_reels","like",0],[3,101,"2024-05-14","ig_reels","like",15],[12,105,"2024-05-13","fb_groups","comment",40],[5,102,"2024-05-07","ig_reels","ig_save",20],[9,103,"2024-05-21","ig_reels","comment",5],[2,101,"2024-05-08","ig_reels","ig_save",20],[11,104,"2024-05-13","ig_reels","like",10],[4,101,"2024-05-15","ig_reels","comment",10],[8,103,"2024-05-20","ig_reels","lik

Output[["102","2024-05-13","8","20","60.00"],["101","2024-05-13","25","60","58.33"]]

Practice SQL

PostgreSQL

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

Up next

Reels Cohort 7-Day Rolling RetentionEasy

Compare Reels Signup Cohort EngagementHard

IG Reels Posting SpanHard

Next question