题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
WITH filtered_plays AS (
SELECT
MONTH(p.fdate) AS month,
p.song_id,
COUNT(*) AS play_pv
FROM
play_log p
JOIN
user_info u ON p.user_id = u.user_id
AND u.age BETWEEN 18 AND 25
WHERE
YEAR(p.fdate) = 2022
GROUP BY
MONTH(p.fdate), p.song_id
),
songs AS (
SELECT * FROM song_info WHERE singer_name = '周杰伦'
)
SELECT
month,
ranking,
song_name,
play_pv
FROM (
SELECT
f.month,
s.song_name,
f.play_pv,
ROW_NUMBER() OVER(PARTITION BY f.month ORDER BY f.play_pv DESC) AS ranking
FROM
filtered_plays f
JOIN
songs s ON f.song_id = s.song_id
) ranked
WHERE
ranking <= 3
ORDER BY
month asc, ranking asc;
拆解成两个CTE清晰一些，
以及这里不能用rank()over() 不然就跳跃排序了~
查看12道真题和解析
