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

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