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

1SELECT column1, aggregate_function(column2)  
2FROM table_name  
3GROUP 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

1SELECT user_id, COUNT(series_id) AS total_shows_watched  
2FROM watch_history  
3GROUP 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

1SELECT user_id, AVG(watch_duration_minutes) AS avg_watch_time  
2FROM watch_history  
3GROUP 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.