Top-N per Group - Netflix Content Rankings

intermediate

Find the top 3 most-watched shows in each genre

-- Netflix-style content ranking pattern

WITH content_rankings AS (

SELECT

show_title,

genre,

total_watch_hours,

unique_viewers,

-- Rank by watch hours within each genre

RANK() OVER (

PARTITION BY genre

ORDER BY total_watch_hours DESC

) as watch_hours_rank,

-- Also rank by unique viewers for comparison

RANK() OVER (

PARTITION BY genre

ORDER BY unique_viewers DESC

) as viewers_rank

FROM content_analytics

WHERE analysis_date >= CURRENT_DATE - INTERVAL '30 days'

)

SELECT

genre,

show_title,

total_watch_hours,

unique_viewers,

watch_hours_rank,

viewers_rank

FROM content_rankings

WHERE watch_hours_rank <= 3 -- Top 3 per genre

ORDER BY genre, watch_hours_rank;

genre | show_title | total_watch_hours | unique_viewers | watch_hours_rank | viewers_rank

Action | Extraction 2 | 45000000 | 12000000 | 1 | 1

Action | The Gray Man | 38000000 | 10500000 | 2 | 2

Action | Red Notice | 32000000 | 9800000 | 3 | 3

Comedy | The Adam Project | 28000000 | 8500000 | 1 | 1

Comedy | Don't Look Up | 25000000 | 7900000 | 2 | 2

Comedy | Glass Onion | 22000000 | 7200000 | 3 | 3