46. Rank Channels by Total Video Views
Solution ↗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 solution to rank channels based on the total views accumulated by all their videos.
Return the result table with the columns channel_name, total_video_views, and view_rank.
Rank the channels in descending order of total video views. If multiple channels have the same total number of views, they should receive the same rank.
Return the result table in any order.
Hint
Tip: First aggregate the total views at the channel level, then apply a window function to assign ranks.
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 | total_video_views | view_rank | +----------------+-------------------+-----------+ | Data With Mira | 4000000 | 1 | | Alpha Tech | 4000000 | 1 | | SQL Zone | 800000 | 3 | +----------------+-------------------+-----------+
Explanation:
Alpha Tech has 1,500,000 + 2,500,000 = 4,000,000 total video views.
Data With Mira has 3,000,000 + 1,000,000 = 4,000,000 total video views.
SQL Zone has 800,000 total video views.
Since Alpha Tech and Data With Mira have the same total, both receive rank 1. The next rank is 3, which is assigned to SQL Zone.