Youtube mid JOINWHEREGROUP BYCASE WHENCOUNT(DISTINCT)ROUND

For each video, calculate the percentage of viewers who watched until the end out of all viewers who started the video. Round the result to 2 decimal places.

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

📖 Story Youtube · Engagement Analytics

YouTube HQ, Monday product review. The Creator Growth team notices that while some videos attract thousands of viewers, not everyone watches until the end. The Product team wants to identify which videos keep viewers engaged throughout the entire video before recommending them to more users.

🎯 Your Mission

📋 Table Structure

🗂 videos

video_id INTEGER 101

title TEXT SQL Basics

duration_seconds INTEGER 600

🗂 watch_history

watch_id INTEGER 1001

user_id INTEGER 1

video_id INTEGER 101

watch_seconds INTEGER 600

watch_date TEXT 2024-05-01

⚡ Step-by-Step Walkthrough

Join tables and identify viewers who started or completed the video

query.sql

SELECT w.user_id,
       v.title,
       v.duration_seconds,
       w.watch_seconds
FROM watch_history w
JOIN videos v ON w.video_id = v.video_id
WHERE w.watch_seconds > 0;

💡 Explanation

Two separate tables contain the information we need. watch_history stores how many seconds each user watched, while videos stores the total duration of every video. Neither table alone can calculate viewer retention.
JOIN videos v ON w.video_id = v.video_id links every watch record to its corresponding video. This allows us to compare how long a user watched against the video's total duration.
WHERE w.watch_seconds > 0 removes invalid watch records where a user never actually watched the video. Only users who watched at least one second are considered as viewers who started the video.
Why filter this early? Rows that do not represent actual viewing activity add unnecessary data to the aggregation step and can produce misleading retention numbers.
The result of this step is a clean dataset where every watch record contains both the video's duration and the user's watch time. This prepares the data for calculating started and completed viewers in the next step.

Group by video and split started vs completed viewers using CASE WHEN

query.sql

SELECT v.title,
       COUNT(DISTINCT CASE
               WHEN w.watch_seconds > 0
               THEN w.user_id END) AS started_viewers,
       COUNT(DISTINCT CASE
               WHEN w.watch_seconds >= v.duration_seconds
               THEN w.user_id END) AS completed_viewers
FROM watch_history w
JOIN videos v ON w.video_id = v.video_id
WHERE w.watch_seconds > 0
GROUP BY v.title
ORDER BY v.title;

💡 Explanation

GROUP BY v.title combines all watch records belonging to the same video into a single row. Instead of seeing one row per viewer, we'll see one row per video.
CASE WHEN w.watch_seconds > 0 THEN w.user_id END identifies users who actually started watching the video. COUNT(DISTINCT) ensures every viewer is counted only once, even if duplicate watch records exist.
The second CASE WHEN checks whether a viewer watched at least the video's full duration. If watch_seconds is greater than or equal to duration_seconds, that viewer is considered to have completed the video.
This approach is called conditional aggregation. It allows us to calculate multiple metrics from the same dataset inside one GROUP BY query without writing separate queries.
Run this step to verify the output. Each video should now have two numbers: started_viewers and completed_viewers.
These are raw counts only. The next step converts them into viewer retention percentages.

Calculate viewer retention percentage and round to 2 decimal places

query.sql

SELECT v.title,
       ROUND(
         COUNT(DISTINCT CASE
                 WHEN w.watch_seconds >= v.duration_seconds
                 THEN w.user_id END) * 100.0
         /
         COUNT(DISTINCT CASE
                 WHEN w.watch_seconds > 0
                 THEN w.user_id END), 2
       ) AS retention_percentage
FROM watch_history w
JOIN videos v ON w.video_id = v.video_id
WHERE w.watch_seconds > 0
GROUP BY v.title
ORDER BY retention_percentage DESC;

💡 Explanation

Viewer Retention 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, ensuring the retention percentage is calculated accurately.
ROUND(expression, 2) wraps the entire percentage calculation and limits the result to exactly two decimal places. The second argument specifies the number of digits after the decimal point.
Notice that we reuse the same CASE WHEN expressions from Step 2. No new aggregation logic is introduced here. We are simply converting the raw counts into a business metric.
ORDER BY retention_percentage DESC ranks videos from the highest retention rate to the lowest. Videos with better retention are usually stronger candidates for recommendation because viewers stay engaged until the end.
Expected result: SQL Basics → 75.00%, Python Crash Course → 62.50%, Data Engineering Roadmap → 50.00%. Higher retention indicates viewers are consistently watching the complete video.

▶ Practice this live ← Browse all questions