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

# 年龄 2022年 每个月 播放次数前三 周杰伦
with
t1 as(
    select
        cast(date_format(fdate,'%m') as unsigned) as month,
        song_name,
        song_id
    from
        play_log
        left join user_info using(user_id)
        left join song_info using(song_id)
    where
        fdate between '2022-01-01' and '2022-12-31'
        and
        singer_name='周杰伦'
        and
        age between 18 and 25
),
t2 as(
    select distinct
        month,
        song_name,
        song_id,
        count(song_name)over(partition by month,song_name)as play_pv
    from
        t1
),
t3 as(
    select
        month,
        dense_rank()over(partition by month order by play_pv desc,song_id) as ranking,
        song_name,
        song_id,
        play_pv
    from
        t2
)

select
    month,
    ranking,
    song_name,
    play_pv
from
    t3
where
    ranking between 1 and 3