Interview Guides

Instagram Reels DAU/MAU Ratio | Dataford Interview Questions - Dataford - Ace your Interview

Instagram Reels DAU/MAU Ratio

Hard

SQL & Data Manipulation

Asked at 2 companies2

Also asked at

Problem

Task

Meta’s Instagram Reels team tracks stickiness using the DAU/MAU ratio. Write a SQL query to compute this ratio for each of the past 30 days using Reels engagement activity.

For this question, define:

DAU = distinct users active on that calendar day
MAU = distinct users active in the 30-day rolling window ending on that day
DAU/MAU ratio = dau / mau

Use the reels_events table below.

Requirements

Return one row per day for the past 30 days ending on 2024-06-30.
Count a user as active if they generated at least one event on Instagram Reels that day.
For each day, compute the rolling 30-day MAU ending on that day.
Return activity_date, dau, mau, and dau_mau_ratio rounded to 4 decimal places.
Order the output by activity_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Reels action such as `reels_play`, `ig_save`, or `reels_share`
event_time	TIMESTAMP	Time of the event
surface	VARCHAR(50)	Product surface where the event happened
country_code	VARCHAR(2)	User country code

Sample Data

event_id	user_id	event_name	event_time	surface	country_code
101	1001	reels_play	2024-06-30 09:10:00	reels	US
102	1002	ig_save	2024-06-30 11:00:00	reels	IN
103	1001	reels_share	2024-06-29 08:00:00	reels	US
104	1003	reels_play	2024-06-28 15:30:00	reels	BR
105	1004	reels_play	2024-06-15 12:00:00	reels	US
106	1005	reels_play	2024-06-01 10:00:00	reels	GB
107	1006	reels_play	2024-05-31 18:00:00	reels	CA
108	1002	reels_play	2024-05-20 09:00:00	reels	IN
109	1007	reels_play	2024-06-30 14:00:00	fb_groups	US
110	1008	reels_play	NULL	reels	US

Expected Output

activity_date	dau	mau	dau_mau_ratio
2024-06-28	1	6	0.1667
2024-06-29	1	6	0.1667
2024-06-30	2	6	0.3333

Schema

reels_events

Column	Type	Description
event_idPK	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Type of Instagram Reels event
event_time	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Meta product surface where the event happened
country_code	VARCHAR(2)	Two-letter country code

Sample Data

Example 1

Input

{"reels_events":[[101,1001,"reels_play","2024-06-30 09:10:00","reels","US"],[102,1002,"ig_save","2024-06-30 11:00:00","reels","IN"],[103,1001,"reels_share","2024-06-29 08:00:00","reels","US"],[104,1003,"reels_play","2024-06-28 15:30:00","reels","BR"],[105,1004,"reels_play","2024-06-15 12:00:00","reels","US"],[106,1005,"reels_play","2024-06-01 10:00:00","reels","GB"],[107,1006,"reels_play","2024-05-31 18:00:00","reels","CA"],[108,1002,"reels_play","2024-05-20 09:00:00","reels","IN"],[109,1007,"re

Output

[["2024-06-01","1","3","0.3333"],["2024-06-02","1","4","0.2500"],["2024-06-03","0","4","0.0000"],["2024-06-04","0","4","0.0000"],["2024-06-05","0","4","0.0000"],["2024-06-06","0","4","0.0000"],["2024-06-07","0","4","0.0000"],["2024-06-08","0","4","0.0000"],["2024-06-09","0","4","0.0000"],["2024-06-10","1","5","0.2000"],["2024-06-11","0","5","0.0000"],["2024-06-12","0","5","0.0000"],["2024-06-13","0","5","0.0000"],["2024-06-14","0","5","0.0000"],["2024-06-15","1","6","0.1667"],["2024-06-16","0","

Problem

Task

Meta’s Instagram Reels team tracks stickiness using the DAU/MAU ratio. Write a SQL query to compute this ratio for each of the past 30 days using Reels engagement activity.

For this question, define:

DAU = distinct users active on that calendar day
MAU = distinct users active in the 30-day rolling window ending on that day
DAU/MAU ratio = dau / mau

Use the reels_events table below.

Requirements

Return one row per day for the past 30 days ending on 2024-06-30.
Count a user as active if they generated at least one event on Instagram Reels that day.
For each day, compute the rolling 30-day MAU ending on that day.
Return activity_date, dau, mau, and dau_mau_ratio rounded to 4 decimal places.
Order the output by activity_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Reels action such as `reels_play`, `ig_save`, or `reels_share`
event_time	TIMESTAMP	Time of the event
surface	VARCHAR(50)	Product surface where the event happened
country_code	VARCHAR(2)	User country code

Sample Data

event_id	user_id	event_name	event_time	surface	country_code
101	1001	reels_play	2024-06-30 09:10:00	reels	US
102	1002	ig_save	2024-06-30 11:00:00	reels	IN
103	1001	reels_share	2024-06-29 08:00:00	reels	US
104	1003	reels_play	2024-06-28 15:30:00	reels	BR
105	1004	reels_play	2024-06-15 12:00:00	reels	US
106	1005	reels_play	2024-06-01 10:00:00	reels	GB
107	1006	reels_play	2024-05-31 18:00:00	reels	CA
108	1002	reels_play	2024-05-20 09:00:00	reels	IN
109	1007	reels_play	2024-06-30 14:00:00	fb_groups	US
110	1008	reels_play	NULL	reels	US

Expected Output

activity_date	dau	mau	dau_mau_ratio
2024-06-28	1	6	0.1667
2024-06-29	1	6	0.1667
2024-06-30	2	6	0.3333

Schema

reels_events

Column	Type	Description
event_idPK	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Type of Instagram Reels event
event_time	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Meta product surface where the event happened
country_code	VARCHAR(2)	Two-letter country code

Sample Data

Example 1

Input

{"reels_events":[[101,1001,"reels_play","2024-06-30 09:10:00","reels","US"],[102,1002,"ig_save","2024-06-30 11:00:00","reels","IN"],[103,1001,"reels_share","2024-06-29 08:00:00","reels","US"],[104,1003,"reels_play","2024-06-28 15:30:00","reels","BR"],[105,1004,"reels_play","2024-06-15 12:00:00","reels","US"],[106,1005,"reels_play","2024-06-01 10:00:00","reels","GB"],[107,1006,"reels_play","2024-05-31 18:00:00","reels","CA"],[108,1002,"reels_play","2024-05-20 09:00:00","reels","IN"],[109,1007,"re

Output

[["2024-06-01","1","3","0.3333"],["2024-06-02","1","4","0.2500"],["2024-06-03","0","4","0.0000"],["2024-06-04","0","4","0.0000"],["2024-06-05","0","4","0.0000"],["2024-06-06","0","4","0.0000"],["2024-06-07","0","4","0.0000"],["2024-06-08","0","4","0.0000"],["2024-06-09","0","4","0.0000"],["2024-06-10","1","5","0.2000"],["2024-06-11","0","5","0.0000"],["2024-06-12","0","5","0.0000"],["2024-06-13","0","5","0.0000"],["2024-06-14","0","5","0.0000"],["2024-06-15","1","6","0.1667"],["2024-06-16","0","

Practice SQL

PostgreSQL

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

Up next

Top Countries by DAU GrowthMedium

Reels Cohort 7-Day Rolling RetentionEasy

IG Reels Posting SpanHard

Next question

Instagram Reels DAU/MAU Ratio

Hard

SQL & Data Manipulation

Asked at 2 companies2

Also asked at

Problem

Task

Meta’s Instagram Reels team tracks stickiness using the DAU/MAU ratio. Write a SQL query to compute this ratio for each of the past 30 days using Reels engagement activity.

For this question, define:

DAU = distinct users active on that calendar day
MAU = distinct users active in the 30-day rolling window ending on that day
DAU/MAU ratio = dau / mau

Use the reels_events table below.

Requirements

Return one row per day for the past 30 days ending on 2024-06-30.
Count a user as active if they generated at least one event on Instagram Reels that day.
For each day, compute the rolling 30-day MAU ending on that day.
Return activity_date, dau, mau, and dau_mau_ratio rounded to 4 decimal places.
Order the output by activity_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Reels action such as `reels_play`, `ig_save`, or `reels_share`
event_time	TIMESTAMP	Time of the event
surface	VARCHAR(50)	Product surface where the event happened
country_code	VARCHAR(2)	User country code

Sample Data

event_id	user_id	event_name	event_time	surface	country_code
101	1001	reels_play	2024-06-30 09:10:00	reels	US
102	1002	ig_save	2024-06-30 11:00:00	reels	IN
103	1001	reels_share	2024-06-29 08:00:00	reels	US
104	1003	reels_play	2024-06-28 15:30:00	reels	BR
105	1004	reels_play	2024-06-15 12:00:00	reels	US
106	1005	reels_play	2024-06-01 10:00:00	reels	GB
107	1006	reels_play	2024-05-31 18:00:00	reels	CA
108	1002	reels_play	2024-05-20 09:00:00	reels	IN
109	1007	reels_play	2024-06-30 14:00:00	fb_groups	US
110	1008	reels_play	NULL	reels	US

Expected Output

activity_date	dau	mau	dau_mau_ratio
2024-06-28	1	6	0.1667
2024-06-29	1	6	0.1667
2024-06-30	2	6	0.3333

Schema

reels_events

Column	Type	Description
event_idPK	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Type of Instagram Reels event
event_time	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Meta product surface where the event happened
country_code	VARCHAR(2)	Two-letter country code

Sample Data

Example 1

Input

{"reels_events":[[101,1001,"reels_play","2024-06-30 09:10:00","reels","US"],[102,1002,"ig_save","2024-06-30 11:00:00","reels","IN"],[103,1001,"reels_share","2024-06-29 08:00:00","reels","US"],[104,1003,"reels_play","2024-06-28 15:30:00","reels","BR"],[105,1004,"reels_play","2024-06-15 12:00:00","reels","US"],[106,1005,"reels_play","2024-06-01 10:00:00","reels","GB"],[107,1006,"reels_play","2024-05-31 18:00:00","reels","CA"],[108,1002,"reels_play","2024-05-20 09:00:00","reels","IN"],[109,1007,"re

Output

[["2024-06-01","1","3","0.3333"],["2024-06-02","1","4","0.2500"],["2024-06-03","0","4","0.0000"],["2024-06-04","0","4","0.0000"],["2024-06-05","0","4","0.0000"],["2024-06-06","0","4","0.0000"],["2024-06-07","0","4","0.0000"],["2024-06-08","0","4","0.0000"],["2024-06-09","0","4","0.0000"],["2024-06-10","1","5","0.2000"],["2024-06-11","0","5","0.0000"],["2024-06-12","0","5","0.0000"],["2024-06-13","0","5","0.0000"],["2024-06-14","0","5","0.0000"],["2024-06-15","1","6","0.1667"],["2024-06-16","0","

Problem

Task

Meta’s Instagram Reels team tracks stickiness using the DAU/MAU ratio. Write a SQL query to compute this ratio for each of the past 30 days using Reels engagement activity.

For this question, define:

DAU = distinct users active on that calendar day
MAU = distinct users active in the 30-day rolling window ending on that day
DAU/MAU ratio = dau / mau

Use the reels_events table below.

Requirements

Return one row per day for the past 30 days ending on 2024-06-30.
Count a user as active if they generated at least one event on Instagram Reels that day.
For each day, compute the rolling 30-day MAU ending on that day.
Return activity_date, dau, mau, and dau_mau_ratio rounded to 4 decimal places.
Order the output by activity_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Reels action such as `reels_play`, `ig_save`, or `reels_share`
event_time	TIMESTAMP	Time of the event
surface	VARCHAR(50)	Product surface where the event happened
country_code	VARCHAR(2)	User country code

Sample Data

event_id	user_id	event_name	event_time	surface	country_code
101	1001	reels_play	2024-06-30 09:10:00	reels	US
102	1002	ig_save	2024-06-30 11:00:00	reels	IN
103	1001	reels_share	2024-06-29 08:00:00	reels	US
104	1003	reels_play	2024-06-28 15:30:00	reels	BR
105	1004	reels_play	2024-06-15 12:00:00	reels	US
106	1005	reels_play	2024-06-01 10:00:00	reels	GB
107	1006	reels_play	2024-05-31 18:00:00	reels	CA
108	1002	reels_play	2024-05-20 09:00:00	reels	IN
109	1007	reels_play	2024-06-30 14:00:00	fb_groups	US
110	1008	reels_play	NULL	reels	US

Expected Output

activity_date	dau	mau	dau_mau_ratio
2024-06-28	1	6	0.1667
2024-06-29	1	6	0.1667
2024-06-30	2	6	0.3333

Schema

reels_events

Column	Type	Description
event_idPK	INT	Primary key for each Reels event
user_id	INT	Meta user identifier
event_name	VARCHAR(50)	Type of Instagram Reels event
event_time	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Meta product surface where the event happened
country_code	VARCHAR(2)	Two-letter country code

Sample Data

Example 1

Input

{"reels_events":[[101,1001,"reels_play","2024-06-30 09:10:00","reels","US"],[102,1002,"ig_save","2024-06-30 11:00:00","reels","IN"],[103,1001,"reels_share","2024-06-29 08:00:00","reels","US"],[104,1003,"reels_play","2024-06-28 15:30:00","reels","BR"],[105,1004,"reels_play","2024-06-15 12:00:00","reels","US"],[106,1005,"reels_play","2024-06-01 10:00:00","reels","GB"],[107,1006,"reels_play","2024-05-31 18:00:00","reels","CA"],[108,1002,"reels_play","2024-05-20 09:00:00","reels","IN"],[109,1007,"re

Output

[["2024-06-01","1","3","0.3333"],["2024-06-02","1","4","0.2500"],["2024-06-03","0","4","0.0000"],["2024-06-04","0","4","0.0000"],["2024-06-05","0","4","0.0000"],["2024-06-06","0","4","0.0000"],["2024-06-07","0","4","0.0000"],["2024-06-08","0","4","0.0000"],["2024-06-09","0","4","0.0000"],["2024-06-10","1","5","0.2000"],["2024-06-11","0","5","0.0000"],["2024-06-12","0","5","0.0000"],["2024-06-13","0","5","0.0000"],["2024-06-14","0","5","0.0000"],["2024-06-15","1","6","0.1667"],["2024-06-16","0","

Practice SQL

PostgreSQL

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

Up next

Top Countries by DAU GrowthMedium

Reels Cohort 7-Day Rolling RetentionEasy

IG Reels Posting SpanHard

Next question