Top Categories Per User | Dataford Interview Questions

Write a SQL query using window functions to identify the top three most-watched content categories for each Roku user over the last 30 days.

Use the provided Roku user, content category, and viewing event tables. Treat total watch seconds as the measure of category viewership.

Output

One row per qualifying user and category, with user_id, username, category_name, total_watch_seconds, and category_rank
Include only the top three categories per user, breaking ties alphabetically by category name
Order by user_id, then category_rank

roku_users

Column	Type	Description
user_idPK	INT	Unique Roku user identifier
username	VARCHAR(100)	Roku user display name

content_categories

Column	Type	Description
category_idPK	INT	Unique content category identifier
category_name	VARCHAR(100)	Content category name

viewing_events

Column	Type	Description
event_idPK	INT	Unique viewing event identifier
user_id	INT	Roku user who watched the content
category_id	INT	Content category associated with the event
watched_at	TIMESTAMP	Timestamp when the viewing occurred
watch_seconds	INT	Number of seconds watched

Your question is Top Categories Per User. Start with the requirements and the three 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.