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

with t as(

select

    month(a.fdate) month,

    row_number() over(partition by month(a.fdate) order by a.song_id,count(1)desc) ranking,

    b.song_name,

    count(1) play_pv

from

    play_log a

    join

    (select * from song_info where singer_name='周杰伦')b

on a.song_id=b.song_id

where

    a.user_id in

    (select user_id from user_info where age>=18 and age<=25)

    and

    year(a.fdate)=2022

group by

    month(a.fdate),

    a.song_id,

    b.song_name

)

select * from t where ranking<=3;