7-Day Rolling Active Users | Dataford Interview Questions

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred

Your question is 7-Day Rolling Active Users. 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