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

select
    *
from
    (
        select
            fdate as month,
            row_number() over (
                partition by
                    fdate
                order by
                    play_pv desc,song_id asc
            ) as ranking,
            song_name as song_name,
            play_pv
        from
            (
                select
                    month (p.fdate) as fdate,
                    s.song_id,
                    song_name,
                    count(1) as play_pv
                from
                    play_log p,
                    song_info s,
                    user_info u
                where
                    p.song_id = s.song_id
                    and p.user_id = u.user_id
                    and s.singer_name = '周杰伦'
                    and year(p.fdate) = 2022
                    and u.age between 18 and 25
                group by
                    month (p.fdate),
                    s.song_id,
                    song_name
            ) t1
    ) t2
where
    ranking < 4