Filtering Grouped Data with HAVING

Hello! Welcome to the final lesson in our module on calculating core product metrics.

Introduction

In our last lesson, we combined JOINs and GROUP BY to calculate powerful cross-table metrics, like the average number of events per user. You learned how to bring data together from multiple tables and then segment it to generate summary statistics.

However, a common next step in analysis is to filter your results based on those very statistics. For example, once you've calculated the number of events for every user, you might want to find only the "power users" who have more than 100 events. The WHERE clause, as you know, filters rows before they are grouped and aggregated, so it can't be used for this purpose.

This is where the HAVING clause comes in. This lesson is dedicated to mastering this final piece of the aggregation puzzle. By the end of our 60 minutes, you will be able to filter grouped data using HAVING to find segments that meet certain criteria, such as identifying features used by more than 100 users or countries with the highest engagement.

1. The Core Concept: WHERE vs. HAVING

The most important—and sometimes confusing—concept to grasp is the difference between WHERE and HAVING. Both are used for filtering, but they operate at different stages of a query's execution.

• WHERE filters individual rows before aggregation. Think of it as selecting which raw ingredients to use before you start cooking.
• HAVING filters entire groups after aggregation. This is like inspecting the finished cakes and only keeping the ones that weigh more than a certain amount.

Because of this, a SQL query follows a logical order of operations:

1. FROM / JOIN: Gathers and combines the data.
2. WHERE: Filters individual rows.
3. GROUP BY: Organizes the remaining rows into groups.
4. HAVING: Filters the groups themselves based on an aggregate calculation.
5. SELECT: Determines the final columns to display.
6. ORDER BY: Sorts the final result.

This video provides a clear, visual explanation of the HAVING clause and its relationship to GROUP BY and WHERE.

2. Applying the HAVING Clause in Practice

Now that you understand the "why," let's focus on the "how." The syntax is straightforward: the HAVING clause is placed directly after the GROUP BY clause.

A common mistake for beginners is trying to use an aggregate function like COUNT() or AVG() in the WHERE clause. The next video demonstrates exactly why this fails and how HAVING solves the problem.

As you saw, HAVING is essential for asking questions about the aggregates themselves.

3. Combining WHERE and HAVING for Powerful Insights

The real power comes from using WHERE and HAVING in the same query. This allows you to perform a two-step filtering process:

1. WHERE: Pre-filters the raw data to define the dataset you want to analyze (e.g., only active users, only events from the last 30 days).
2. HAVING: Post-filters the grouped results to find segments that meet your criteria (e.g., only user segments with more than 10 people, only features with an average session time over 60 seconds).

Example Scenario: As a product designer, you might want to find which features are most engaging for your newly acquired users.

SELECT
    feature_name,
    COUNT(DISTINCT user_id) AS number_of_users
FROM
    events
WHERE
    -- Step 1: Filter for new users first
    user_signup_date >= '2024-01-01' 
GROUP BY
    feature_name
HAVING
    -- Step 2: Filter for highly used features among this group
    COUNT(DISTINCT user_id) > 1000 
ORDER BY
    number_of_users DESC;

This query first narrows the data down to events from new users (WHERE), then groups by feature, and finally filters those groups to show only the features that more than 1000 of these new users have interacted with (HAVING).

For a concise reference, the following article provides excellent examples and a clear comparison table.

4. Practice: Finding Your Most Engaged User Segments

Let's return to the music streaming service scenario from our last lesson. You have access to the same tables: users, play_history, and songs.

• users (user_id, country, signup_date)
• play_history (play_id, user_id, song_id, play_timestamp)
• songs (song_id, artist_name, duration_seconds)

Write one SQL query for each of the following questions.

1. Identify Power Listeners: We want to find our most engaged users. Write a query to find the user_id of all users who have played more than 50 songs.
2. Popular Artists in a Key Market: The marketing team is planning a campaign in 'Germany'. They want to know which artists are already popular there. Write a query to find the artist_names that have been played more than 20 times by users from 'Germany'.

Click to see the solutions

1. Identify Power Listeners:

   SELECT
       user_id,
       COUNT(play_id) AS songs_played
   FROM
       play_history
   GROUP BY
       user_id
   HAVING
       COUNT(play_id) > 50
   ORDER BY
       songs_played DESC;
   

   • We GROUP BY user_id to count plays for each user.
   • We use HAVING COUNT(play_id) > 50 to filter these groups, keeping only the users who meet our "power listener" criteria.

2. Popular Artists in Germany:

   SELECT
       s.artist_name,
       COUNT(p.play_id) AS play_count_in_germany
   FROM
       users AS u
   JOIN
       play_history AS p ON u.user_id = p.user_id
   JOIN
       songs AS s ON p.song_id = s.song_id
   WHERE
       u.country = 'Germany'
   GROUP BY
       s.artist_name
   HAVING
       COUNT(p.play_id) > 20
   ORDER BY
       play_count_in_germany DESC;
   

   • First, we JOIN the three tables to link users' countries to the artists they play.
   • The WHERE u.country = 'Germany' clause pre-filters the data to include only plays from German users.
   • Then, we GROUP BY s.artist_name to count the plays for each artist within that filtered dataset.
   • Finally, HAVING COUNT(p.play_id) > 20 filters these artist groups to find only those with significant popularity in Germany.

Conclusion

Congratulations on completing this lesson and the "Calculating Core Product Metrics" module! You've now mastered one of the most critical skills in data analysis: the ability to not only aggregate data into meaningful metrics but also to filter those results to find the most important insights.

Key Takeaways:

• The HAVING clause is used to filter data based on the results of aggregate functions (like COUNT(), SUM(), AVG()).
• WHERE filters individual rows before grouping, while HAVING filters entire groups after grouping.
• You can combine WHERE and HAVING in a single query to first narrow down your initial dataset and then filter the aggregated results.

What's Next?

You now have a robust toolkit for querying and analyzing product data. You can select data, filter it, sort it, join it from multiple tables, and calculate and filter complex metrics. The next steps in your SQL journey would typically involve more advanced topics like working with date and time functions to analyze trends, or using powerful "window functions" to perform calculations like rankings and running totals. These tools build directly on the foundation you've established here and will allow you to answer even more sophisticated product questions.

Well done