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

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

select
    month
    , ranking
    , song_name
    , play_pv
from tmp
having ranking <= 3