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

select 
`month`
,ranking
,song_name
,play_pv
from (
    select 
    month(t1.fdate) as `month`
    ,rank() over(partition by month(t1.fdate) order by count(t2.song_id) desc,  t2.song_id asc) ranking
    ,t2.song_name
    ,count(t1.song_id) as  play_pv
    from
    (
        select fdate
            ,user_id
            ,song_id
        from play_log 
        where year(fdate) = 2022
    ) t1
    inner join
    (
        select song_id
            ,song_name
        from song_info
        where singer_name = '周杰伦'
    ) t2
    on t1.song_id =t2.song_id
    inner join 
    (
        select user_id
        from user_info
        where age between 18 and 25
    ) t3
    on t1.user_id =t3.user_id
    group by month(t1.fdate),t2.song_id,t2.song_name
) t4
where ranking <= 3