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

#处理用户和歌单表关联后表
with base as (select month(fdate) as month,
                     song_name,
                     count(1)     as play_pv,
                     song.song_id as song_id
              from play_log play
                       join song_info song on play.song_id = song.song_id
                  and song.singer_name = '周杰伦'
                       join user_info user on play.user_id = user.user_id
                  and (
                                                  user.age >= 18
                                                      and user.age < 25
                                                  )
              WHERE YEAR(play.fdate) = '2022'
              group by month(fdate), song_name,song_id),
     ranks as (select month,
                      row_number() over (partition by month order by play_pv desc,song_id ASC ) as ranking,
                      song_name,
                      play_pv
               from base)
select *
from ranks
where ranking <= 3
;