3-Month Rolling Average in SQL | Dataford Interview Questions

Your question is 3-Month Rolling Average in SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

The wants to monitor monthly engagement across its user-facing products. Write a PostgreSQL query that calculates each user's rolling average number of interactions for the three calendar months ending in April 2025.

Requirements

Aggregate interaction events by user and calendar month.
Include every user and every month from January through April 2025, including users or months with no interactions.
Use a window function to calculate the average for the current month and the two preceding calendar months.
Return only the April 2025 result, with one row per user.
Treat missing monthly interaction counts as zero, and sort by user_id.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
signup_date	DATE	Date the user registered

user_interactions

Column	Type	Description
interaction_idPK	INTEGER	Unique interaction identifier
user_id	INTEGER	User associated with the interaction
interaction_at	TIMESTAMP	Timestamp when the interaction occurred
interaction_type	VARCHAR(40)	Interaction category