Top 3 Genres Per User | Dataford Interview Questions

Your question is Top 3 Genres Per User. 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

Paramount+ wants a personalized summary of the genres each subscriber watched most during the previous 30 days. Assume the reporting timestamp is 2025-02-01 00:00:00, and viewing records at or after that timestamp are excluded.

Write a PostgreSQL query to identify the top three genres for each user, ranked by total watch time.

Requirements

Join users, viewing events, and titles to associate watch time with users and genres.
Include only events from the 30-day interval ending immediately before the reporting timestamp.
Aggregate watch time by user and genre, ignoring events with missing titles, genres, or watch durations.
Rank genres independently for each user and return only ranks 1 through 3.
Return user_id, user_name, genre, total_watch_seconds, and genre_rank, ordered by user and rank.

Schema

users

Column	Type	Description
user_idPK	INT	Subscriber identifier
user_name	VARCHAR(100)	Subscriber display name

viewing_events

Column	Type	Description
event_idPK	INT	Viewing event identifier
user_id	INT	Subscriber who generated the event
title_id	INT	Watched title identifier
watched_at	TIMESTAMP	Timestamp when viewing occurred
watch_seconds	INT	Seconds watched during the event

titles

Column	Type	Description
title_idPK	INT	Paramount+ title identifier
title_name	VARCHAR(200)	Title name
genre	VARCHAR(50)	Title genre