Solution: 44. Most Viewed Video per Channel

Hard

Problem Detail:

Write a SQL query to find the most viewed video for each channel.

Answer SQL Query:

SELECT c.channel_name, v.title, s.view_count
FROM channels c
JOIN videos v
  ON c.channel_id = v.channel_id
JOIN video_stats s
  ON v.video_id = s.video_id
WHERE s.view_count = (
    SELECT MAX(s2.view_count)
    FROM videos v2
    JOIN video_stats s2
      ON v2.video_id = s2.video_id
    WHERE v2.channel_id = v.channel_id
);

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