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

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

)
select month, ranking,song_name, play_pv
from m1 
where ranking <=3;