Solution: 50. Latest Video Performing Above Channel Average

WITH latest_video AS (
    SELECT v.channel_id,
           v.video_id,
           v.title,
           v.published_at,
           s.view_count,
           ROW_NUMBER() OVER (
               PARTITION BY v.channel_id
               ORDER BY v.published_at DESC
           ) AS rn
    FROM videos v
    JOIN video_stats s
      ON v.video_id = s.video_id
),
channel_avg AS (
    SELECT v.channel_id,
           AVG(s.view_count) AS avg_views
    FROM videos v
    JOIN video_stats s
      ON v.video_id = s.video_id
    GROUP BY v.channel_id
)
SELECT c.channel_name,
       lv.title AS latest_video_title,
       lv.view_count AS latest_video_views,
       ca.avg_views
FROM latest_video lv
JOIN channel_avg ca
  ON lv.channel_id = ca.channel_id
JOIN channels c
  ON lv.channel_id = c.channel_id
WHERE lv.rn = 1
  AND lv.view_count > ca.avg_views;