Window Functions for Engagement | Dataford Interview Questions

AARP.org wants to monitor engagement trends for active users. Write a PostgreSQL query that aggregates engagement by user and day, then calculates both cumulative engagement and a three-day moving average.

Requirements

Join users to engagement events and include only users whose account_status is active.
Aggregate engagement minutes by user and engagement_date for January 1 through January 5, 2025. Treat a day containing only NULL minutes as zero.
Calculate each user's running total using a window function ordered by date.
Calculate a three-row moving average of daily engagement, including the current day and up to the two preceding observed engagement dates. Round it to two decimal places.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
display_name	VARCHAR(100)	User's display name
account_status	VARCHAR(20)	Current account status

engagement_events

Column	Type	Description
event_idPK	INTEGER	Unique engagement event identifier
user_id	INTEGER	References users.user_id
engagement_date	DATE	Date of the engagement event
engagement_minutes	INTEGER	Minutes attributed to the event

Your question is Window Functions for Engagement. 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