Rolling Average with SQL Windows | Dataford Interview Questions

RpmGlobal wants to monitor engagement with Enterprise Planning. Calculate each eligible user's rolling average of daily user-initiated activity for the reporting period 2025-02-01 through 2025-02-08.

Requirements

Include only users whose account_status is active and whose product_area is Enterprise Planning.
Count only model_run, schedule_update, and dashboard_view events. Treat dates without qualifying events as zero activity.
Return one row per eligible user per calendar date, with daily_activity and a 30-calendar-day rolling average, including the current date.
Round the rolling average to four decimal places and sort by user, then date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
full_name	VARCHAR(100)	User's display name
account_status	VARCHAR(20)	Account state, such as active or inactive
product_area	VARCHAR(100)	RpmGlobal product area available to the user

activity_events

Column	Type	Description
activity_idPK	INTEGER	Unique activity event identifier
user_id	INTEGER	User associated with the activity event
occurred_at	TIMESTAMP	Timestamp when the event occurred
activity_type	VARCHAR(50)	Type of recorded product activity

Your question is Rolling Average with SQL Windows. 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