Moving Average With Window Functions | Dataford Interview Questions

Hotstar wants to monitor how viewer engagement changes over time. Write a PostgreSQL query that calculates each user's session length and their moving average session length over the preceding 30 days, including the current session.

Requirements

Exclude incomplete sessions and sessions without a valid start timestamp.
Join viewer_sessions with users to include the viewer's name.
Use a window function partitioned by user and ordered by session_start.
Return results ordered by user_id and session start time.

Schema

users

Column	Type	Description
user_idPK	INT	Unique viewer identifier
user_name	VARCHAR(100)	Viewer name
subscription_tier	VARCHAR(20)	Hotstar subscription tier

viewer_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	Viewer identifier
session_start	TIMESTAMP	Session start timestamp
session_end	TIMESTAMP	Session end timestamp

Your question is Moving Average With Window Functions. 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