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

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