🔥 SQL Interview Question of the Day

📌 Scenario:

A streaming platform wants to find the most watched movie in each genre.

You have one table:

watch_history

• user_id
• movie_id
• movie_name
• genre
• watch_time_minutes

☑ Solution:

SELECT
genre,
movie_name,
total_watch_time
FROM (
SELECT
genre,
movie_name,
SUM(watch_time_minutes) AS total_watch_time,
RANK() OVER (
PARTITION BY genre
ORDER BY SUM(watch_time_minutes) DESC
) AS rnk
FROM watch_history
GROUP BY genre, movie_name
) t
WHERE rnk = 1;

💡 Concept Tested:

Window Functions + RANK() + GROUP BY + Aggregate Functions

❤️ React if you want more SQL interview questions.