Netflix mid JOINWHERE INGROUP BYCASE WHENCOUNT(DISTINCT)ROUND

For each TV show, calculate the completion percentage based on users who started Episode 1 versus users who watched the final episode. Round the result to 2 decimal places.

Netflix · Engagement Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Netflix · Engagement Analytics

Netflix HQ, Monday content review. The Content Analytics team notices that while some TV shows attract lots of viewers, not everyone reaches the final episode. The VP of Content wants to know which shows keep viewers engaged until the end before deciding which ones deserve another season.

🎯 Your Mission

📋 Table Structure

🗂 shows

show_id INTEGER 101

title TEXT Dark Secrets

total_episodes INTEGER 8

🗂 watch_history

watch_id INTEGER 1001

user_id INTEGER 1

show_id INTEGER 101

episode_no INTEGER 1

watch_date TEXT 2024-05-01

⚡ Step-by-Step Walkthrough

Join tables and keep only Episode 1 and Final Episode

query.sql

SELECT w.user_id,
       s.title,
       s.total_episodes,
       w.episode_no
FROM watch_history w
JOIN shows s
ON w.show_id = s.show_id
WHERE w.episode_no IN (1, s.total_episodes);

💡 Explanation

The information we need is stored across two tables. watch_history tells us which episode each user watched, while shows tells us how many episodes exist in every TV show. Neither table alone is enough to calculate the completion percentage.
JOIN shows s ON w.show_id = s.show_id links every watch record to its TV show. This allows us to compare the watched episode with the total number of episodes in that show.
WHERE w.episode_no IN (1, s.total_episodes) keeps only two important events: users who started the show by watching Episode 1 and users who reached the final episode.
Why filter this early? Intermediate episodes do not help us calculate completion percentage. Keeping Episodes 2, 3, 4, and so on would only increase the amount of data that GROUP BY has to process in the next step.
The result of this step is a clean dataset containing only Episode 1 and Final Episode records for every show. This makes the aggregation step much simpler.

Group by TV show and count started vs completed viewers using CASE WHEN

query.sql

SELECT s.title,
       COUNT(DISTINCT CASE
                        WHEN w.episode_no = 1
                        THEN w.user_id
                      END) AS started_viewers,
       COUNT(DISTINCT CASE
                        WHEN w.episode_no = s.total_episodes
                        THEN w.user_id
                      END) AS completed_viewers
FROM watch_history w
JOIN shows s
ON w.show_id = s.show_id
WHERE w.episode_no IN (1, s.total_episodes)
GROUP BY s.title
ORDER BY s.title;

💡 Explanation

GROUP BY s.title combines all rows belonging to the same TV show into a single row. Instead of seeing one row per viewing activity, we'll see one row per show.
CASE WHEN w.episode_no = 1 THEN w.user_id END keeps only users who started the show. COUNT(DISTINCT) counts each viewer only once, even if duplicate watch records exist.
The second CASE WHEN checks whether the watched episode is the show's final episode. COUNT(DISTINCT) then gives the total number of viewers who completed the series.
This technique is called conditional aggregation. It allows us to calculate multiple metrics from a single dataset within one GROUP BY query, without writing multiple queries.
Run this step to verify that every show has two raw numbers: started_viewers and completed_viewers. These are counts only, not percentages yet.
The next step converts these raw counts into completion percentages.

Calculate completion percentage and round to 2 decimal places

query.sql

SELECT s.title,
       ROUND(
         COUNT(DISTINCT CASE
                          WHEN w.episode_no = s.total_episodes
                          THEN w.user_id
                        END) * 100.0
         /
         COUNT(DISTINCT CASE
                          WHEN w.episode_no = 1
                          THEN w.user_id
                        END), 2
       ) AS completion_percentage
FROM watch_history w
JOIN shows s
ON w.show_id = s.show_id
WHERE w.episode_no IN (1, s.total_episodes)
GROUP BY s.title
ORDER BY completion_percentage DESC;

💡 Explanation

Completion Percentage follows a simple business formula: Completed Viewers ÷ Started Viewers × 100. Since Step 2 already calculated both values, we simply apply the formula here.
Why multiply by 100.0 instead of 100? SQL performs integer division when both values are integers. Using 100.0 forces decimal arithmetic, giving an accurate percentage instead of truncating the value.
ROUND(expression, 2) trims the calculated percentage to exactly two decimal places. The second argument tells SQL how many digits should appear after the decimal point.
Notice that we reuse the same CASE WHEN expressions from Step 2. We are not introducing any new aggregation logic, only converting the raw counts into a business metric.
ORDER BY completion_percentage DESC ranks the TV shows from the highest completion rate to the lowest, making it easy for the Content team to identify shows that keep viewers engaged until the finale.
Expected result: Campus Days → 60.00%, Dark Secrets → 50.00%, Galaxy Wars → 33.33%. Shows with higher completion percentages generally have stronger viewer retention.

▶ Practice this live ← Browse all questions