Aggregating Joined Data

Hello! Welcome back to your course on SQL for product designers.

Introduction

In our previous lessons, you've built a solid foundation. You learned how to use JOIN to connect related data from different tables (like users and events) and how to use GROUP BY with aggregate functions (COUNT, AVG, etc.) to calculate metrics for different segments within a single table.

Today, we'll combine these two powerful concepts. As a product designer, your most insightful questions often live at the intersection of different datasets. You don't just want to know how many "add to cart" events happened; you want to know which users performed them and how that behavior varies across user segments.

This lesson focuses on exactly that. By the end of our 60 minutes, you will be able to combine joins with aggregate functions to calculate cross-table metrics, such as the average number of events per user. This is a cornerstone of product analytics, enabling you to move from simple reporting to deep, actionable insights about user behavior.

1. The Logic: Why We Need Both JOIN and GROUP BY

Let's start with a typical product analytics question: "How many purchases has each customer made?"

To answer this, you need information from two tables:

1. A customers table (to get customer names or IDs).
2. An orders table (to count the purchases).

Here's the logical flow of how SQL tackles this:

1. JOIN the Tables: First, SQL needs to create a temporary, large table that links each customer to all of their orders. This is done using a JOIN on a common key, like customer_id.
2. GROUP BY the Segments: Next, SQL takes this large, combined table and splits it into groups, one for each customer.
3. COUNT the Records: Finally, it applies an aggregate function, like COUNT(), to each group to count the number of orders for that specific customer.

This process allows you to perform calculations on one table (orders) while segmenting the results by a column from another table (customers).

2. Calculating Metrics Across Two Tables

Let's see this in action. The following article provides a clear, step-by-step guide using users and cities tables. It demonstrates how to calculate various metrics (like the number of users or the average age) for each city.

The Critical Importance of LEFT JOIN

As you saw in the article, using a standard JOIN (an INNER JOIN) will only give you results for cities that have users. The city "Coyote Springs," which has no users, was excluded.

In product analytics, this is a critical distinction. If you want to find the number of events for every user, you must use a LEFT JOIN.

• INNER JOIN: Shows you users who have performed an action.
• LEFT JOIN: Shows you ALL users, and indicates which ones have or have not performed an action (often resulting in NULL or 0 values for inactive users).

As a product designer, you're often just as interested in the users who aren't doing something as the ones who are. Therefore, when calculating metrics per user, your default should almost always be LEFT JOIN, starting from the users table.

Query Structure for "Events Per User":

SELECT
  u.user_id,
  COUNT(e.event_id) AS number_of_events
FROM
  users AS u -- The 'left' table, includes ALL users
LEFT JOIN
  events AS e ON u.user_id = e.user_id
GROUP BY
  u.user_id;

This query guarantees that every single user from your users table will be in the result, with a count of their events. Inactive users will show a count of 0.

3. Scaling Up: Analyzing Across Multiple Tables

Real-world product databases are often more complex, requiring you to join three, four, or even more tables to get the answer you need. The logic remains the same, but the query gets longer.

The following video demonstrates how to build a complex query step-by-step, joining four different tables to get a complete picture. The instructor's thought process is particularly valuable.

The key takeaway from the video is that even in a multi-table JOIN, the principle of LEFT JOIN remains vital. You establish your primary "entity" (for him, employees; for you, users) and use LEFT JOINs to progressively add more information without accidentally losing anyone from that primary group.

4. Practice: Analyzing Product Usage

You are a product designer at a music streaming service. You have access to the following tables:

• users
  • user_id (Primary Key)
  • country
  • signup_date
• play_history
  • play_id (Primary Key)
  • user_id (Foreign Key to users)
  • song_id (Foreign Key to songs)
  • play_timestamp
• songs
  • song_id (Primary Key)
  • artist_name
  • duration_seconds

Write one SQL query for each of the following questions.

1. Calculate the total number of songs played by each user. Your final table should have two columns: user_id and total_songs_played. Crucially, ensure that users who have never played a song are included in your list with a count of 0.
2. Find the average song duration (in seconds) that users in each country listen to. Your final table should have country and avg_duration.

Click to see the solutions

1. Total songs played per user:

   SELECT
     u.user_id,
     COUNT(p.song_id) AS total_songs_played
   FROM
     users AS u
   LEFT JOIN
     play_history AS p ON u.user_id = p.user_id
   GROUP BY
     u.user_id
   ORDER BY
     total_songs_played DESC;
   

   • We use LEFT JOIN starting from users to ensure all users are included.
   • We COUNT(p.song_id) because COUNT on a specific column ignores NULL values. For users with no plays, p.song_id will be NULL, resulting in a correct count of 0.

2. Average song duration per country:

   SELECT
     u.country,
     AVG(s.duration_seconds) AS avg_duration
   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
   GROUP BY
     u.country;
   

   • Here, we need to join all three tables to link a user's country to the duration of the songs they played.
   • An INNER JOIN is acceptable here because we can only calculate an average duration for countries where listening has actually occurred.

Conclusion

Fantastic work today! You have now connected two of the most important concepts in SQL for analytics. By combining JOIN and GROUP BY, you can now compute sophisticated, cross-table metrics that are at the heart of understanding user behavior and product performance.

Key Takeaways:

• To calculate metrics from one table segmented by data from another, you must combine JOIN and GROUP BY.
• SQL logically performs the JOIN first to create a combined dataset, then uses GROUP BY to segment that dataset, and finally applies aggregate functions.
• When analyzing user behavior, use LEFT JOIN starting from the users table to ensure you include all users, even those with no activity. This prevents you from getting a skewed view of your user base.

Next Lesson Preview:

You can now calculate metrics for different groups, but what if you want to filter based on the metric itself? For example, how would you find "all users who have played more than 100 songs" or "features that are used by less than 5% of users"? The WHERE clause can't be used on aggregated results. In our next lesson, we will introduce the HAVING clause, which is designed specifically for filtering your data after it has been grouped.