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

/*
连表-分组-窗口排序-选ranking < 4基本就是这个套路
逆天的是题目没有说明排序条件......

首先看输出案例，先按month, ranking，pv排是没有问题
关键最后pv一样的话按什么排？按song_name asc和desc都不行，那只有按song_id排序了（因为是要对歌曲排序）
*/
select
    month_ as month,
    ranking,
    song_name,
    pv as play_pv 
from
    (
        select
            month_,
            row_number() over(partition by month_ order by pv desc, song_id) as ranking,
            song_name,
            pv
        from
            ( 
                select
                    s.song_id,
                    month(fdate) as month_,
                    song_name,
                    count(*) as pv
                from 
                    song_info as s
                    inner join play_log as p on s.song_id=p.song_id
                    and year(fdate)=2022 and singer_name="周杰伦" 
                    and exists(
                        select 1
                        from user_info
                        where user_info.user_id=p.user_id and user_info.age between 18 and 25
                    )
                group by 
                    month(fdate), song_name, s.song_id  
            ) as grouped_tb
    ) as ranking_tb
where ranking < 4
order by month, ranking