Rolling 30-Day Average Query | Dataford Interview Questions

Beyond Finance wants to monitor daily sign-ups for its debt-relief application funnel. Write a PostgreSQL query that calculates the rolling 30-day average of valid user sign-ups for the reporting period from February 1 through February 5, 2024.

Requirements

Count each user only once per sign-up date, even if duplicate sign-up events exist.
Exclude test users and events that are not sign-up events.
Treat days with no valid sign-ups as zero and calculate the average across the current day and preceding 29 calendar days.
Return the reporting date, daily sign-ups, and rolling average, ordered chronologically.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
is_test_user	BOOLEAN	Indicates whether the account is for testing

user_events

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
user_id	INTEGER	User associated with the event
event_type	VARCHAR(30)	Type of user funnel event
occurred_at	TIMESTAMP	Timestamp when the event occurred

Your question is Rolling 30-Day Average Query. 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