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

# 从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

with tmp as (
    select *,
    row_number() over(partition by month order by play_pv desc ,song_id asc) as ranking
from 
(select 
month(fdate) month,
a.song_id,
song_name,
count(*) play_pv
from play_log a
join user_info b on a.user_id=b.user_id
join song_info c on a.song_id= c.song_id
where year(fdate) = 2022
      and age between 18 and 25
      and singer_name = '周杰伦'
group by month(fdate),song_id,song_name
) a
)

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