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

-- user_id 18-25岁
-- 2022年每个月
-- 周杰伦的歌曲

WITH T AS (SELECT P.user_id, MONTH(fdate) AS month, P.song_id , song_name
FROM play_log P 
LEFT JOIN user_info U 
ON P.user_id = U.user_id 

LEFT JOIN song_info S 
ON P.song_id = S.song_id

WHERE U.age >= 18 AND U.age <= 25
AND YEAR(fdate) = 2022 
AND S.singer_name = "周杰伦"),

T2 AS (SELECT month, song_name, song_id,
COUNT(*)
AS play_pv
FROM T
GROUP BY month, song_name, song_id),

T3 AS (SELECT month, song_name, play_pv, 
ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC, song_id)
AS RN
FROM T2)

SELECT month, RN AS ranking, song_name, play_pv FROM T3 WHERE RN <= 3