Interview Guides

7-Day Rolling DAU on Facebook | Dataford Interview Questions

Asked 1mo ago|

Stripe

7-Day Rolling DAU on Facebook

Hard

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.

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

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.

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

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling Active UsersMedium

Rolling 7-Day Average Active UsersMedium

Rolling 7-Day Active UsersMedium

Next question

Asked 1mo ago|

Stripe

7-Day Rolling DAU on Facebook

Hard

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.

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

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.

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

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling Active UsersMedium

Rolling 7-Day Average Active UsersMedium

Rolling 7-Day Active UsersMedium

Next question