HAVING

🎬 Scenario: Finding Active Netflix Users#

Your manager at Netflix, Alex, wants to identify power users—those who have watched at least 3 different series.

Can you find users who have watched at least 3 series?

Your task is to query the watch_history table and filter results based on aggregated values.

1. Understanding the Difference Between WHERE and HAVING#

Both WHERE and HAVING allow us to filter data, but they work differently:

Example Comparison

Filtering Before Aggregation (WHERE)
Find all watch sessions that lasted more than 30 minutes:

SELECT user_id, series_id, watch_duration_minutes  
FROM watch_history  
WHERE watch_duration_minutes > 30;

Filtering After Aggregation (HAVING)
Find users who have watched at least 3 different series:

1SELECT user_id, COUNT(series_id) AS total_shows_watched  
2FROM watch_history  
3GROUP BY user_id  
4HAVING COUNT(series_id) >= 3;

2. Using HAVING to Filter Aggregated Results#

Since HAVING works after aggregation, we can use it to filter users based on how many shows they watched.

Example Query: Finding Users Who Watched 3+ Series

1SELECT user_id, COUNT(series_id) AS total_shows_watched  
2FROM watch_history  
3GROUP BY user_id  
4HAVING COUNT(series_id) >= 3;

What’s Happening?

• COUNT(series_id) calculates how many different shows each user has watched.
• HAVING COUNT(series_id) >= 3 filters the result after aggregation, keeping only users who watched at least 3 series.

3. Combining WHERE and HAVING#

You can use WHERE and HAVING together in a query.

For example, if we want to:

• Exclude very short watch sessions (WHERE watch_duration_minutes > 30)
• Find users who watched at least 2 series (HAVING COUNT(series_id) >= 2)

We can write:

1SELECT user_id, COUNT(series_id) AS total_shows_watched  
2FROM watch_history  
3WHERE watch_duration_minutes > 30  
4GROUP BY user_id  
5HAVING COUNT(series_id) >= 2;

What’s Happening?

• WHERE watch_duration_minutes > 30 filters out watch sessions before aggregation.
• GROUP BY user_id groups data by user.
• HAVING COUNT(series_id) >= 2 filters users based on the aggregated count.

✍️ SQL Exercises#

Exercise 1: Finding Power Users#

Alex wants a report of users who have watched at least 3 different series.

Filter condition:

• Users must have watched at least 3 series.
• Sort by total_shows_watched in descending order.

Write an SQL query to return the requested data.

Exercise 2: Filtering Users Who Watched 2+ Series with Longer Sessions#

Alex also wants to identify users who watched at least 2 series but only counting watch sessions longer than 30 minutes.

Filter condition:

• Exclude watch sessions that lasted 30 minutes or less.
• Count how many series each user watched.
• Only include users who watched at least 2 series.

Write an SQL query to return the requested data.