50. Latest Video Performing Above Channel Average

Solution ↗

Hard

Tables: channels, videos, video_stats

+-------------------------+-------------+
| Column Name             | Type        |
+-------------------------+-------------+
| channel_id              | varchar     |
| channel_name            | varchar     |
| subscriber_count        | bigint      |
| view_count              | bigint      |
+-------------------------+-------------+
channels contains one row per YouTube channel.
channel_id is the primary key for this table.

+-------------------------+-------------+
| Column Name             | Type        |
+-------------------------+-------------+
| video_id                | varchar     |
| channel_id              | varchar     |
| title                   | varchar     |
| published_at            | datetime    |
+-------------------------+-------------+
videos contains one row per video.
video_id is the primary key for this table.
channel_id is a foreign key referencing channels.channel_id.

+-------------------------+-------------+
| Column Name             | Type        |
+-------------------------+-------------+
| video_id                | varchar     |
| view_count              | bigint      |
| like_count              | bigint      |
| comment_count           | bigint      |
+-------------------------+-------------+
video_stats contains performance metrics for each video.
video_id is the primary key for this table and references videos.video_id.

Write a SQL query to find channels where the latest uploaded video has more views than that channel's average video views.

Hint

Tip: Use one CTE for the latest video and another for channel averages.

Input:

channels table:
+------------+------------------+------------------+------------+
| channel_id | channel_name     | subscriber_count | view_count |
+------------+------------------+------------------+------------+
| C101       | Alpha Tech       | 2500000          | 92000000   |
| C102       | Data With Mira   | 1800000          | 71000000   |
| C103       | SQL Zone         | 950000           | 40000000   |
+------------+------------------+------------------+------------+

videos table:
+----------+------------+-------------------+---------------------+
| video_id | channel_id | title             | published_at        |
+----------+------------+-------------------+---------------------+
| V1       | C101       | Python Basics     | 2024-01-11 10:00:00 |
| V2       | C101       | Tableau Project   | 2024-03-05 10:00:00 |
| V3       | C102       | SQL Joins         | 2024-04-10 09:00:00 |
| V4       | C102       | Window Functions  | 2024-05-01 09:00:00 |
| V5       | C103       | Intro to MySQL    | 2024-02-07 11:30:00 |
+----------+------------+-------------------+---------------------+

video_stats table:
+----------+------------+------------+---------------+
| video_id | view_count | like_count | comment_count |
+----------+------------+------------+---------------+
| V1       | 1500000    | 91000      | 4200          |
| V2       | 2500000    | 135000     | 6100          |
| V3       | 3000000    | 150000     | 8000          |
| V4       | 1000000    | 47000      | 2500          |
| V5       | 800000     | 36000      | 1400          |
+----------+------------+------------+---------------+

Output:

Output:
+----------------+--------------------+--------------------+-----------+
| channel_name   | latest_video_title | latest_video_views | avg_views |
+----------------+--------------------+--------------------+-----------+
| Data With Mira | Window Functions   | 1000000            | 2000000   |
+----------------+--------------------+--------------------+-----------+

Explanation:

The latest video for each channel is compared against that channel's average video views. Only rows satisfying the condition are returned. The exact sample output can vary based on data.