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

with
    tb_month as (
        select
            # SUBSTR(TO_CHAR(fdate,'yyyymmdd'),1,4 ) as year,
            #        SUBSTR(TO_CHAR(fdate,'yyyymmdd'),5,2 ) as month,
            month(fdate) as month,
            year(fdate) as year,
            fdate,
            user_id,
            song_id
        from
            play_log
    )
select
    *
from
    (
        select
            tm.month,
            Row_NUMBER() over (
                partition by
                    tm.month
                order by
                    count(tm.song_id) desc,
                    tm.song_id asc
            ) as ranking,
            si.song_name,
            count(tm.song_id) play_pv
        from
            song_info si
            inner join tb_month tm on tm.song_id = si.song_id
            left join user_info ui on ui.user_id = tm.user_id
        where
            si.singer_name = '周杰伦'
            and ui.age between 18 and 25
            and tm.year = 2022
        group by
            tm.month,
            si.song_name,
            si.song_id
    ) rt
where
    ranking <= 3;