7-Day Rolling User Average in SQL | Dataford Interview Questions

Given a dataset with a timestamp column and user events, write a SQL query to compute a 7-day rolling average of events per user using window functions.

Use the provided user, event, and calendar-date tables. Treat dates without events as zero-event days.

Output

One row per user and calendar date.
Columns: user_id, event_date, daily_event_count, and rolling_7_day_avg.
Include all users and calendar dates, sort by user_id, then event_date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_timestamp	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(50)	Type of user event

calendar_dates

Column	Type	Description
calendar_datePK	DATE	Calendar date included in the reporting range

Your question is 7-Day Rolling User Average in SQL. Start with the requirements and the three 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