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

with t as (
    select MONTH(fdate) as month,
           row_number() over(partition by month(t1.fdate) order by count(t2.song_name) desc, t1.song_id asc ) as ranking, 
           t2.song_name,
           count(t2.song_name) as play_pv
           
    from play_log  t1
    left join song_info  t2
    on t1.song_id=t2.song_id
    left join user_info  t3
    on t1.user_id=t3.user_id
    where (YEAR(t1.fdate)=2022) and 
          (t3.age >=18 and t3.age<=25) and
           (t2.singer_name='周杰伦')
    group bY MONTH(fdate),t1.song_id,t2.song_name
)
select * 
from t 
where ranking <=3;