SQL Rolling Three-Day Average | Dataford Interview Questions

The BlueLabs Activity Insights surface needs a daily engagement trend for each user. Write a PostgreSQL query that calculates a rolling three-calendar-day average of activity.

Requirements

Aggregate interaction counts by user and calendar date.
Include every BlueLabs user and every date between the earliest and latest interaction, treating missing activity as zero.
Use a window function to calculate the three-day average for each user, including the current date and two preceding calendar days.
Return user_id, user_name, activity_date, and the rounded average, ordered by user and date.

Schema

users

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

user_interactions

Column	Type	Description
interaction_idPK	INTEGER	Unique interaction identifier
user_id	INTEGER	References users.user_id
interaction_date	DATE	Calendar date of the interaction
activity_count	INTEGER	Number of activities in the interaction record

Your question is SQL Rolling Three-Day Average. 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