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

select *
from (
    select fdate_month as month,
        row_number() over(partition by fdate_month order by count(song_name) desc,song_id asc) ranking,
        song_name,
        count(song_name) play_pv
    from (
        select month(pl.fdate) as fdate_month,
            pl.song_id,
            si.song_name
        from play_log as pl
        left join song_info as si on pl.song_id = si.song_id
        left join user_info as ui on ui.user_id = pl.user_id
        where (ui.age between 18 and 25)
        and year(pl.fdate) = '2022'
        and si.singer_name = '周杰伦'
        )a
    group by fdate_month,song_name,song_id
)b
where b.ranking <= 3