SQL题练习—分组内TOP3

初学者超级超级希望大家提代码建议或学习建议！！！

问题分析：从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

1.表连接：

select *
from  play_log as p
join song_info as s on p.song_id=s.song_id
join user_info as u on p.user_id=u.user_id;

2.条件查询：年龄：18-25岁 between 18 and 25     时间：2022年 year(fdate)=2022

 歌手：周杰伦  singer_name='周杰伦'      按月分组：group by month(fdate)    

top3：播放次数 count(user_id)   

3.窗口函数：分组内排名 row_number()over()

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