GROUP BY

🎬 Scenario: Analyzing User Watch Patterns#

Your manager at Netflix, Alex, wants insights into how users engage with the platform. They need a summary of:

• The total number of shows watched per user
• The average watch duration per user
• The longest and shortest watch session per user

Alex asks:

Can you group user watch data and provide aggregated statistics for each user?

Your task is to query the watch_history table and provide the requested insights.

1. Understanding GROUP BY#

The GROUP BY clause allows us to group data by a specific column and apply aggregate functions to summarize the results.

Syntax

SELECT column1, aggregate_function(column2)  
FROM table_name  
GROUP BY column1;

• GROUP BY column1 groups rows that share the same value in column1.
• The aggregate function performs calculations on the grouped rows.

2. Counting Shows Watched per User#

To find the total number of shows watched per user, we can use COUNT() to count the number of series_id values for each user.

Example Query: Counting Shows Watched

SELECT user_id, COUNT(series_id) AS total_shows_watched  
FROM watch_history  
GROUP BY user_id;

What’s Happening?

• COUNT(series_id) counts how many different shows each user has watched.
• GROUP BY user_id ensures each row in the output represents a unique user.

3. Calculating Average Watch Duration#

To find the average time users spend watching, we use AVG() on the watch_duration_minutes column.

Example Query: Average Watch Duration Per User

SELECT user_id, AVG(watch_duration_minutes) AS avg_watch_time  
FROM watch_history  
GROUP BY user_id;

What’s Happening?

• AVG(watch_duration_minutes) calculates the average watch time per user.
• GROUP BY user_id ensures each row represents a single user.

4. Finding Maximum & Minimum Watch Duration#

We can use MAX() and MIN() to determine the longest and shortest viewing session for each user.

Example Query: Longest & Shortest Watch Session

1SELECT user_id,  
2       MAX(watch_duration_minutes) AS max_watch_time,  
3       MIN(watch_duration_minutes) AS min_watch_time  
4FROM watch_history  
5GROUP BY user_id;

What’s Happening?

• MAX(watch_duration_minutes) returns the longest watch session for each user.
• MIN(watch_duration_minutes) returns the shortest watch session per user.

✍️ SQL Exercises#

Exercise 1: Counting the Number of Shows Watched per User#

Alex wants to know how many shows each user has watched.

Filter condition:

• Count the number of series_id each user has watched.
• Sort by total_shows_watched in descending order.

Write an SQL query to return the requested data.

Exercise 2: Finding the Average Watch Duration per User#

Alex also wants to see how much time each user spends watching on average.

Filter condition:

• Calculate the average watch duration for each user.
• Sort by avg_watch_time in descending order.

Write an SQL query to return the requested data.