Solution: 46. Rank Channels by Total Video Views

Medium

Problem Detail:

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.

Answer SQL Query:

WITH channel_views AS (
    SELECT c.channel_id,
           c.channel_name,
           SUM(s.view_count) AS total_video_views
    FROM channels c
    JOIN videos v
      ON c.channel_id = v.channel_id
    JOIN video_stats s
      ON v.video_id = s.video_id
    GROUP BY c.channel_id, c.channel_name
)
SELECT channel_name,
       total_video_views,
       RANK() OVER (ORDER BY total_video_views DESC) AS view_rank
FROM channel_views;

You can easily copy this solution, switch back to your editor tab, paste it, and run the SQL!