7-Day Rolling DAU on Facebook | Dataford Interview Questions

Welcome to the SQL screen.

The question is on your right: 7-Day Rolling DAU on Facebook. Read through the requirements and the one table first.

Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?

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