Running 7-Day Average in SQL | Dataford Interview Questions

Northeastern University’s analytics team monitors engagement in the Northeastern Canvas LMS. Write a PostgreSQL query that calculates daily active users and their trailing seven-calendar-day average.

Requirements

Count distinct valid users with at least one active platform event per calendar day.
Include calendar dates with no active users, treating their daily count as zero.
Calculate the average of the current day and previous six calendar days using a window function. For the first six dates, average all available dates in the range.
Return results in chronological order with the date, daily active-user count, and average rounded to two decimal places.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Northeastern Canvas LMS user identifier
display_name	VARCHAR(100)	User's display name

platform_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
activity_date	DATE	Calendar date on which the event occurred
is_active	BOOLEAN	Whether the event represents active platform use

Your question is Running 7-Day Average in 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