7-Day Rolling DAU on Facebook | Dataford Interview Questions

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.

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

Your question is 7-Day Rolling DAU on Facebook. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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

Your question is 7-Day Rolling DAU on Facebook. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.