/*年龄18-25，时间2022；歌手周杰伦；按月分组播放次数排序；；相同次数要按song_id先后排序*/

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