Rolling Watch Time Window Query | Dataford Interview Questions

Twitch Analytics wants to monitor changes in individual viewer engagement. Write a PostgreSQL query that calculates each active user's rolling seven-day average daily watch time across the available observation period.

Requirements

Aggregate watch_minutes by user and calendar date.
Treat a day without a viewing session, or a session with a NULL duration, as zero watch minutes.
Use a window function to calculate the average for the current date and the six preceding calendar dates for each user.
Return active users in chronological order, with averages rounded to two decimal places.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Twitch viewer identifier
display_name	VARCHAR(50)	Twitch display name
region	VARCHAR(20)	Viewer region

watch_sessions

Column	Type	Description
session_idPK	INTEGER	Unique viewing session identifier
user_id	INTEGER	References users.user_id
session_date	DATE	Calendar date of the viewing session
watch_minutes	INTEGER	Minutes watched in the session
channel_name	VARCHAR(50)	Twitch channel watched

Your question is Rolling Watch Time Window 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