Instagram Reels DAU/MAU Ratio

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

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

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

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

Interview Guides

Task

Requirements

Table Definition

Sample Data

Expected Output

Instagram Reels DAU/MAU Ratio

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL

Instagram Reels DAU/MAU Ratio

Task

Requirements

Table Definition

Sample Data

Expected Output

Instagram Reels DAU/MAU Ratio

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL