题解 | 每个月Top3的周杰伦歌曲

WITH source AS (
  SELECT 
    p.user_id,        
    p.fdate,           
    s.song_name,     
    s.song_id, 
    MONTH(p.fdate) AS month
  FROM play_log p
  JOIN song_info s 
    ON p.song_id = s.song_id
    AND s.singer_name = '周杰伦'
  -- 关联用户表，筛选18-25岁用户
  JOIN user_info u 
    ON u.user_id = p.user_id
    AND u.age BETWEEN 18 AND 25
  -- 筛选2022年的播放记录
  WHERE YEAR(p.fdate) = 2022
),
play_cnt AS (
  SELECT
    month,
    song_name,
    song_id,
    COUNT(song_name) AS play_pv
  FROM source
  GROUP BY month,song_name,song_id
),
ranks AS (
  SELECT
    month,
    ROW_NUMBER() OVER(PARTITION BY month ORDER BY play_pv DESC,song_id) AS ranking,
    song_name,
    play_pv
  FROM play_cnt
)
SELECT * FROM ranks WHERE ranking <= 3