Rolling Averages for DAU | Dataford Interview Questions

Given a specific schema, how would you calculate rolling averages for daily active users at Roblox?

Using the supplied schema, calculate the three-calendar-day rolling average of daily active users from January 1 through January 7, 2025. Treat users with no activity as zero for that date.

Output

One row per calendar date in the requested range
Columns: activity_date, daily_active_users, and rolling_3_day_average
Include dates with no activity, order by activity_date ascending, and round the rolling average to two decimal places.

Schema

roblox_users

Column	Type	Description
user_idPK	INT	Unique Roblox user identifier
username	VARCHAR(100)	Roblox username

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
occurred_at	TIMESTAMP	Timestamp when the activity occurred
event_type	VARCHAR(50)	Type of Roblox activity

Your question is Rolling Averages for DAU. Start with the requirements and the two tables 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