Running Average With Window Functions | Dataford Interview Questions

Hinge's analytics team wants to monitor engagement with the Discover experience. Write a PostgreSQL query that calculates each user's rolling average of daily interactions over the preceding 30 calendar days, including the current interaction date.

Use 2026-03-01 as the report date. Only interactions from 2026-02-01 through 2026-03-01, inclusive, should be considered.

Requirements

Aggregate multiple interactions for the same user on the same date into daily_interactions.
Calculate a 30-day rolling average per user with a window function.
Include only users with at least one interaction in the reporting period.
Return results ordered by user_id and interaction_date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Hinge user identifier
display_name	VARCHAR(100)	User display name

interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction event identifier
user_id	INT	User associated with the interaction
interaction_type	VARCHAR(50)	Type of Hinge interaction
occurred_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is Running Average With Window Functions. 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