Rolling 7-Day Active Averages | Dataford Interview Questions

Business Context

UST's analytics platform needs a daily engagement metric that smooths short-term fluctuations. The metric must count registered active users with at least one activity event and calculate a rolling seven-day average.

Task

Write a PostgreSQL query that returns results for January 1 through January 10, 2025.

Requirements

Generate one row for every calendar date in the reporting period, including dates with no activity.
Count distinct users whose account_status is active and who generated at least one event on each date.
Calculate the rolling seven-day average using a window frame of ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
Round the rolling average to two decimal places and order the output chronologically.

Schema

app_users

Column	Type	Description
user_idPK	INT	Unique registered user identifier
account_status	VARCHAR(20)	Current account status, such as active or suspended

user_activity_events

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
occurred_at	DATE	Calendar date when the event occurred
event_type	VARCHAR(30)	Type of platform activity

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