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

with song as (select convert(right(left(fdate,7),2),unsigned) month, song_name, p.song_id, count(p.song_id) cnt from play_log p left join song_info s on p.song_id = s.song_id left join user_info u on p.user_id = u.user_id where singer_name = ‘周杰伦’ and year(fdate) = ‘2022’ and (age between 18 and 25) group by p.song_id, month, song_name),
song2 as (select month, rank() over( partition by month order by cnt desc,song_id) ranking,song_name ,cnt play_pv, song_id from song)

select month, ranking, song_name, play_pv  from song2 where ranking <= 3 order by month, ranking, song_id

05:41