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

# 18-25岁
# 2022年
# 按月和歌曲分组
# 周杰伦歌曲
# 排序和限制，top3

SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM(
    SELECT
        song_id,
        month(fdate) AS month,
        song_name,
        ROW_NUMBER() OVER(PARTITION BY month(fdate) ORDER BY count(*) DESC,s.song_id) AS ranking,
        count(*) AS play_pv
    FROM play_log p
    LEFT JOIN song_info s USING(song_id)
    LEFT JOIN user_info u USING(user_id)
    WHERE year(fdate) = 2022 AND singer_name = '周杰伦' AND age BETWEEN 18 AND 25
    GROUP BY month(fdate),song_name,song_id
)t1
WHERE ranking <=3
ORDER BY month,play_pv DESC