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

#18-25岁用户 2022年每个月 播放次数TOP3 周杰伦
with cet as(
    select distinct
        month(pl.fdate) as month,
        any_value(pl.user_id) as user_id,
        song_name,
        count(pl.song_id) as play_cnt,
        any_value(si.song_id) as song_id
    from play_log pl
    left join user_info ui on pl.user_id = ui.user_id
    left join song_info si on pl.song_id = si.song_id
    where 
        si.singer_name = "周杰伦"
        and year(pl.fdate) = 2022
        and ui.age between 18 and 25
    group by month,song_name
)

select 
    month,
    ranking,
    song_name,
    play_cnt as play_pv
from (
    select distinct
        month,
        row_number() over(partition by month order by play_cnt desc,song_id) as ranking,
        song_name,
        play_cnt 
    from cet
) as ranked
    
where ranking <= 3