题解 | 每个月Top3的周杰伦歌曲 - 你发如雪 凄美了离别 我焚香感动了谁🎶

with
    tm as (
        select
            month,
            song_name,
            song_id,
            count(*) as play_pv
        from
            (
                select
                    t1.fdate,
                    month (t1.fdate) as month,
                    t1.user_id,
                    t3.age,
                    t1.song_id,
                    t2.song_name,
                    t2.singer_name
                from
                    play_log t1
                    left join song_info t2 on t1.song_id = t2.song_id
                    left join user_info t3 on t1.user_id = t3.user_id
                where
                    (age >= 18)
                    and (age <= 25)
                    and (singer_name = '周杰伦')
            ) as t
        group by
            month,
            song_name,
            song_id
        order by
            month,
            play_pv desc,
            song_id
    )
select
    month,
    rn as ranking,
    song_name,
    play_pv
from
    (
        select
            month,
            row_number() over (
                partition by
                    month
                order by
                    play_pv desc,
                    song_id
            ) as rn,
            song_name,
            play_pv
        from
            tm
    ) as t
where
    rn <= 3

数分离职后复建，面对SQL依然没有好脸色😶‍🌫️