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

with t as (select fdate,month(fdate) as m,pl.user_id,pl.song_id,song_name
from play_log pl
left join song_info si
on pl.song_id = si.song_id

left join user_info on pl.user_id = user_info.user_id 

where year(fdate) = 2022 and singer_name = '周杰伦'and age>=18 and age<=25),
tmp as(
select m,count(*) as cnt,song_id,song_name
from t
group by m,song_id,song_name),qwq as(
select m as month,row_number() over(partition by m order by cnt desc,song_id) as ranking,song_name,cnt as play_pv,song_id
from tmp)
select month,ranking,song_name,play_pv from qwq where ranking <=3
order by month,ranking asc,song_id asc