SQL Window Functions and Rolling Avg | Dataford Interview Questions

Your question is SQL Window Functions and Rolling Avg. Start with the requirements and the three 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

ABC Family wants to monitor daily viewing trends in its streaming app. Write a PostgreSQL query that calculates a three-calendar-day rolling average of completed viewing minutes for each active ABC Family show.

Requirements

Include every date from January 1 through January 5, including dates with no qualifying viewing sessions.
Count only completed sessions from users whose country is US.
Aggregate viewing minutes by show and date, treating missing or NULL minutes as zero.
Calculate the rolling average for the current date and the previous two calendar dates, restarting the window for each show.
Return active ABC Family shows ordered by show title and report date.

Schema

shows

Column	Type	Description
show_idPK	INT	Unique show identifier
show_title	VARCHAR(100)	Show title
network	VARCHAR(50)	Network or platform owner
genre	VARCHAR(50)	Show genre
is_active	BOOLEAN	Whether the show is currently active

users

Column	Type	Description
user_idPK	INT	Unique user identifier
country	VARCHAR(2)	Two-letter user country code

viewing_sessions

Column	Type	Description
session_idPK	INT	Unique viewing session identifier
user_id	INT	User who watched the show
show_id	INT	Show being watched
started_at	DATE	Calendar date when viewing started
duration_minutes	INT	Minutes watched in the session
session_status	VARCHAR(20)	Session completion status