Most Popular Video Creator - Leetcode Solution

The Most Popular Video Creator problem on leetcode is a medium level problem that requires you to find the video creator who has the most number of views from all of their videos.

The problem statement is as follows:

We are given a table named videos with the following schema:

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| video_id        | int     |
| title           | varchar |
| description     | varchar |
| views           | int     |
| created_date    | date    |
+-----------------+---------+

We need to write a SQL query to find the name of the video creator who has the most number of views from all of their videos.

We can approach this problem using a subquery and GROUP BY clause. We can create a subquery to find out the total views for each video creator and then use the GROUP BY clause to group the results by the video creator's name. We can then use the ORDER BY clause to sort the results by the total views in descending order and LIMIT to 1 to get the top result.

SELECT title as creator_name, SUM(views) as total_views
FROM videos
GROUP BY title
ORDER BY total_views DESC
LIMIT 1;

In the above SQL query, we have used the SUM() function to find the total views for each video creator and GROUP BY clause to group the results by the video creator's name. We have used the ORDER BY clause to sort the results by the total views in descending order and LIMIT to 1 to get the top result.

The output of the above SQL query will be the name of the video creator who has the most number of views from all of their videos.

This problem can also be solved using a JOIN clause. We can create a subquery to find out the total views for each video creator and then use a JOIN clause to join the results with the videos table on the video creator's name. We can then use the ORDER BY clause to sort the results by the total views in descending order and LIMIT to 1 to get the top result.

SELECT v.title as creator_name, SUM(v.views) as total_views
FROM videos v
JOIN (
  SELECT title, SUM(views) as views
  FROM videos
  GROUP BY title
) t ON v.title = t.title
GROUP BY v.title
ORDER BY total_views DESC
LIMIT 1;

In the above SQL query, we have created a subquery to find out the total views for each video creator and then used a JOIN clause to join the results with the videos table on the video creator's name. We have used the GROUP BY clause to group the results by the video creator's name and the ORDER BY clause to sort the results by the total views in descending order. We have used the LIMIT clause to get the top result.

The output of the above SQL query will be the name of the video creator who has the most number of views from all of their videos.

1