Top Content by Watch Time | Dataford Interview Questions

Disney+ Hotstar's Product Analytics team wants to identify the strongest-performing content in January 2026. Write a PostgreSQL query that ranks content within each content type using watch-time metrics.

Requirements

Join the content catalog to viewing sessions and include only sessions from January 2026.
Calculate total watch minutes and average completion percentage for each content item. Treat NULL watch minutes as zero.
Use a window function to rank content separately within each content_type.
Return the top two content items per content type, ordered by content type and rank.

Schema

content

Column	Type	Description
content_idPK	INTEGER	Unique content identifier
title	VARCHAR(150)	Content title
content_type	VARCHAR(30)	Content category

viewing_sessions

Column	Type	Description
session_idPK	INTEGER	Unique viewing session identifier
content_id	INTEGER	Watched content identifier
watched_at	DATE	Date of the viewing session
watch_minutes	INTEGER	Minutes watched in the session
completion_pct	INTEGER	Percentage of content completed

Your question is Top Content by Watch Time. 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