Three-Day Rolling Average SQL | Dataford Interview Questions

The TMC analytics platform needs a daily view of user login activity. Write a PostgreSQL query that calculates daily valid logins and a three-day rolling average for 2025-05-01 through 2025-05-07.

Requirements

Count login events for registered users only. Exclude events with a missing timestamp, an unknown user, or a timestamp outside the requested date range.
Include every calendar date in the requested range, including dates with zero valid logins.
Calculate the rolling average over the current date and the preceding two calendar dates. Round the result to two decimal places.
Return results in ascending date order with columns login_date, daily_logins, and three_day_rolling_avg.

Schema

users

Column	Type	Description
user_idPK	INT	Registered user identifier
display_name	VARCHAR(100)	User's display name

user_logins

Column	Type	Description
login_idPK	INT	Login event identifier
user_id	INT	Registered user associated with the login event
login_at	TIMESTAMP	Timestamp when the login occurred

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