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

# 首先找到2022年18-25岁用户听周杰伦的歌的数据
with t1 as (
    select month(fdate) as month, song_id
    from play_log
    where user_id in (select user_id from user_info where age between 18 and 25)
    and year(fdate) = '2022'
    and song_id in (select song_id from song_info where singer_name = '周杰伦')
),
t2 as (
    # 计算每一个月的歌曲被听次数
    select month, t1.song_id, a.song_name, count(*) as play_pv
    from t1
    left join song_info as a
    on a.song_id = t1.song_id
    group by month, t1.song_id, a.song_name
),
t3 as (
    select month, row_number() over(partition by month order by play_pv desc, song_id) as ranking,song_name, play_pv
    from t2
)
select * 
from t3
where ranking <= 3;