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

SELECT *
FROM(SELECT month, ROW_NUMBER() OVER(PARTITION BY month ORDER BY play_pv DESC) AS ranking, song_name, play_pv
     FROM(SELECT DISTINCT MONTH(fdate) AS month, pl.song_id,
          COUNT(fdate) OVER(PARTITION BY MONTH(fdate), pl.song_id) AS play_pv
          FROM play_log pl
          LEFT JOIN user_info ui ON pl.user_id = ui.user_id
          WHERE age BETWEEN 18 AND 25 AND YEAR(fdate) = 2022
          ) a
     LEFT JOIN song_info si ON a.song_id = si.song_id
     WHERE singer_name = '周杰伦'
     ORDER BY month, ranking
    ) b
WHERE ranking <= 3;

全部评论
这个赞给周杰伦
点赞 回复 分享
发布于 01-11 21:10 湖南

相关推荐

评论
1
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务