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.
