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

select month,ranking,song_name,play_pv
from
(
    select month(fdate) month,row_number() over(partition by month(fdate) order by count(t1.song_id) desc,t1.song_id) ranking,song_name,count(t1.song_id) play_pv
    from (play_log t1 join song_info t2 
    on t1.song_id=t2.song_id)  join user_info t3
    on t1.user_id=t3.user_id
    where age between 18 and 25 and year(fdate)='2022' and singer_name='周杰伦'
    group by month(fdate),song_name,t1.song_id) t
where ranking<=3;#2/3用例通过，顺序问题~在窗口函数里加song_id相关排序，此时聚合函数处也要加