Interview Guides

7-Day Rolling DAU on Facebook | Dataford Interview Questions - Dataford - Ace your Interview

7-Day Rolling DAU on Facebook

Hard

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

Task

Facebook wants to monitor short-term engagement trends by tracking the 7-day rolling average of daily active users (DAU). Write a SQL query to calculate this metric from app activity logs.

Requirements

Treat a user as active on a day if they generated at least one event on that date.
Compute daily active users as the count of distinct users per activity date.
Return the 7-day rolling average of DAU using the current date and previous 6 dates in the result set.
Output activity_date, daily_active_users, and rolling_7d_avg_dau.
Order the final result by activity_date ascending.

Table Definitions

`facebook_activity`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

`facebook_activity`

event_id	user_id	event_time	event_name	device_type
5	104	2024-01-02 08:00:00	like	Android
1	101	2024-01-01 09:15:00	login	iPhone
9	106	2024-01-03 11:00:00	comment	Web
3	102	2024-01-01 12:30:00	view_story	Android
12	101	2024-01-04 07:45:00	login	iPhone
16	108	2024-01-05 13:00:00	like	Android
20	109	2024-01-06 18:20:00	view_story	Web
24	110	2024-01-07 09:00:00	login	iPhone
28	111	2024-01-08 14:10:00	comment	Android
31	112	2024-01-09 10:30:00	login	Web

Expected Output

activity_date	daily_active_users	rolling_7d_avg_dau
2024-01-01	2	2.00
2024-01-02	3	2.50
2024-01-03	3	2.67
2024-01-04	3	2.75
2024-01-05	3	2.80
2024-01-06	2	2.67
2024-01-07	3	2.71
2024-01-08	3	2.86
2024-01-09	2	2.71

Schema

facebook_activity

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp when the activity occurred
event_name	VARCHAR(50)	Type of user activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"facebook_activity":[["5","104","2024-01-02 08:00:00","like","Android"],["1","101","2024-01-01 09:15:00","login","iPhone"],["9","106","2024-01-03 11:00:00","comment","Web"],["3","102","2024-01-01 12:30:00","view_story","Android"],["12","101","2024-01-04 07:45:00","login","iPhone"],["16","108","2024-01-05 13:00:00","like","Android"],["20","109","2024-01-06 18:20:00","view_story","Web"],["24","110","2024-01-07 09:00:00","login","iPhone"],["28","111","2024-01-08 14:10:00","comment","Android"],["31

Output

[["2024-01-01","2","2.00"],["2024-01-02","3","2.50"],["2024-01-03","2","2.33"],["2024-01-04","3","2.50"],["2024-01-05","3","2.60"],["2024-01-06","2","2.50"],["2024-01-07","3","2.57"],["2024-01-08","2","2.57"],["2024-01-09","2","2.43"]]

Problem

Task

Facebook wants to monitor short-term engagement trends by tracking the 7-day rolling average of daily active users (DAU). Write a SQL query to calculate this metric from app activity logs.

Requirements

Treat a user as active on a day if they generated at least one event on that date.
Compute daily active users as the count of distinct users per activity date.
Return the 7-day rolling average of DAU using the current date and previous 6 dates in the result set.
Output activity_date, daily_active_users, and rolling_7d_avg_dau.
Order the final result by activity_date ascending.

Table Definitions

`facebook_activity`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

`facebook_activity`

event_id	user_id	event_time	event_name	device_type
5	104	2024-01-02 08:00:00	like	Android
1	101	2024-01-01 09:15:00	login	iPhone
9	106	2024-01-03 11:00:00	comment	Web
3	102	2024-01-01 12:30:00	view_story	Android
12	101	2024-01-04 07:45:00	login	iPhone
16	108	2024-01-05 13:00:00	like	Android
20	109	2024-01-06 18:20:00	view_story	Web
24	110	2024-01-07 09:00:00	login	iPhone
28	111	2024-01-08 14:10:00	comment	Android
31	112	2024-01-09 10:30:00	login	Web

Expected Output

activity_date	daily_active_users	rolling_7d_avg_dau
2024-01-01	2	2.00
2024-01-02	3	2.50
2024-01-03	3	2.67
2024-01-04	3	2.75
2024-01-05	3	2.80
2024-01-06	2	2.67
2024-01-07	3	2.71
2024-01-08	3	2.86
2024-01-09	2	2.71

Schema

facebook_activity

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp when the activity occurred
event_name	VARCHAR(50)	Type of user activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"facebook_activity":[["5","104","2024-01-02 08:00:00","like","Android"],["1","101","2024-01-01 09:15:00","login","iPhone"],["9","106","2024-01-03 11:00:00","comment","Web"],["3","102","2024-01-01 12:30:00","view_story","Android"],["12","101","2024-01-04 07:45:00","login","iPhone"],["16","108","2024-01-05 13:00:00","like","Android"],["20","109","2024-01-06 18:20:00","view_story","Web"],["24","110","2024-01-07 09:00:00","login","iPhone"],["28","111","2024-01-08 14:10:00","comment","Android"],["31

Output

[["2024-01-01","2","2.00"],["2024-01-02","3","2.50"],["2024-01-03","2","2.33"],["2024-01-04","3","2.50"],["2024-01-05","3","2.60"],["2024-01-06","2","2.50"],["2024-01-07","3","2.57"],["2024-01-08","2","2.57"],["2024-01-09","2","2.43"]]

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling Active UsersMedium

Seven-Day Revenue Per User AverageEasy

Top Countries by DAU GrowthMedium

Next question

7-Day Rolling DAU on Facebook

Hard

SQL & Data Manipulation

Asked at 3 companies3

Also asked at

Problem

Task

Facebook wants to monitor short-term engagement trends by tracking the 7-day rolling average of daily active users (DAU). Write a SQL query to calculate this metric from app activity logs.

Requirements

Treat a user as active on a day if they generated at least one event on that date.
Compute daily active users as the count of distinct users per activity date.
Return the 7-day rolling average of DAU using the current date and previous 6 dates in the result set.
Output activity_date, daily_active_users, and rolling_7d_avg_dau.
Order the final result by activity_date ascending.

Table Definitions

`facebook_activity`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

`facebook_activity`

event_id	user_id	event_time	event_name	device_type
5	104	2024-01-02 08:00:00	like	Android
1	101	2024-01-01 09:15:00	login	iPhone
9	106	2024-01-03 11:00:00	comment	Web
3	102	2024-01-01 12:30:00	view_story	Android
12	101	2024-01-04 07:45:00	login	iPhone
16	108	2024-01-05 13:00:00	like	Android
20	109	2024-01-06 18:20:00	view_story	Web
24	110	2024-01-07 09:00:00	login	iPhone
28	111	2024-01-08 14:10:00	comment	Android
31	112	2024-01-09 10:30:00	login	Web

Expected Output

activity_date	daily_active_users	rolling_7d_avg_dau
2024-01-01	2	2.00
2024-01-02	3	2.50
2024-01-03	3	2.67
2024-01-04	3	2.75
2024-01-05	3	2.80
2024-01-06	2	2.67
2024-01-07	3	2.71
2024-01-08	3	2.86
2024-01-09	2	2.71

Schema

facebook_activity

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp when the activity occurred
event_name	VARCHAR(50)	Type of user activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"facebook_activity":[["5","104","2024-01-02 08:00:00","like","Android"],["1","101","2024-01-01 09:15:00","login","iPhone"],["9","106","2024-01-03 11:00:00","comment","Web"],["3","102","2024-01-01 12:30:00","view_story","Android"],["12","101","2024-01-04 07:45:00","login","iPhone"],["16","108","2024-01-05 13:00:00","like","Android"],["20","109","2024-01-06 18:20:00","view_story","Web"],["24","110","2024-01-07 09:00:00","login","iPhone"],["28","111","2024-01-08 14:10:00","comment","Android"],["31

Output

[["2024-01-01","2","2.00"],["2024-01-02","3","2.50"],["2024-01-03","2","2.33"],["2024-01-04","3","2.50"],["2024-01-05","3","2.60"],["2024-01-06","2","2.50"],["2024-01-07","3","2.57"],["2024-01-08","2","2.57"],["2024-01-09","2","2.43"]]

Problem

Task

Facebook wants to monitor short-term engagement trends by tracking the 7-day rolling average of daily active users (DAU). Write a SQL query to calculate this metric from app activity logs.

Requirements

Treat a user as active on a day if they generated at least one event on that date.
Compute daily active users as the count of distinct users per activity date.
Return the 7-day rolling average of DAU using the current date and previous 6 dates in the result set.
Output activity_date, daily_active_users, and rolling_7d_avg_dau.
Order the final result by activity_date ascending.

Table Definitions

`facebook_activity`

Column	Type	Description
event_id	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp of the activity event
event_name	VARCHAR(50)	Type of activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

`facebook_activity`

event_id	user_id	event_time	event_name	device_type
5	104	2024-01-02 08:00:00	like	Android
1	101	2024-01-01 09:15:00	login	iPhone
9	106	2024-01-03 11:00:00	comment	Web
3	102	2024-01-01 12:30:00	view_story	Android
12	101	2024-01-04 07:45:00	login	iPhone
16	108	2024-01-05 13:00:00	like	Android
20	109	2024-01-06 18:20:00	view_story	Web
24	110	2024-01-07 09:00:00	login	iPhone
28	111	2024-01-08 14:10:00	comment	Android
31	112	2024-01-09 10:30:00	login	Web

Expected Output

activity_date	daily_active_users	rolling_7d_avg_dau
2024-01-01	2	2.00
2024-01-02	3	2.50
2024-01-03	3	2.67
2024-01-04	3	2.75
2024-01-05	3	2.80
2024-01-06	2	2.67
2024-01-07	3	2.71
2024-01-08	3	2.86
2024-01-09	2	2.71

Schema

facebook_activity

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	Facebook user who generated the event
event_time	TIMESTAMP	Timestamp when the activity occurred
event_name	VARCHAR(50)	Type of user activity event
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"facebook_activity":[["5","104","2024-01-02 08:00:00","like","Android"],["1","101","2024-01-01 09:15:00","login","iPhone"],["9","106","2024-01-03 11:00:00","comment","Web"],["3","102","2024-01-01 12:30:00","view_story","Android"],["12","101","2024-01-04 07:45:00","login","iPhone"],["16","108","2024-01-05 13:00:00","like","Android"],["20","109","2024-01-06 18:20:00","view_story","Web"],["24","110","2024-01-07 09:00:00","login","iPhone"],["28","111","2024-01-08 14:10:00","comment","Android"],["31

Output

[["2024-01-01","2","2.00"],["2024-01-02","3","2.50"],["2024-01-03","2","2.33"],["2024-01-04","3","2.50"],["2024-01-05","3","2.60"],["2024-01-06","2","2.50"],["2024-01-07","3","2.57"],["2024-01-08","2","2.57"],["2024-01-09","2","2.43"]]

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling Active UsersMedium

Seven-Day Revenue Per User AverageEasy

Top Countries by DAU GrowthMedium

Next question