题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
with s as ( select play_log.fdate as fdate, play_log.user_id as user_id, play_log.song_id as song_id,song_info.song_name as song_name, song_info.singer_name as singer_name, user_info.age as age, row_number() over (order by play_log.fdate) as xulie from play_log left join song_info on play_log.song_id = song_info.song_id left join user_info on play_log.user_id = user_info.user_id ), ss as (select fdate, song_id, song_name, xulie, SUBSTRING(CAST(fdate AS CHAR), 6, 2) AS month_part, CASE WHEN SUBSTRING(CAST(fdate AS CHAR), 6, 1) = '0' THEN SUBSTRING(CAST(fdate AS CHAR), 7, 1) ELSE SUBSTRING(CAST(fdate AS CHAR), 6, 2) END AS month from s where singer_name = '周杰伦' and age between 18 and 25 and year(fdate) = 2022 ), sss as( select month,song_name, count(xulie) as play_pv,song_id,month_part from ss group by month,song_name,song_id,month_part order by song_name), ssss as (select month,song_name,play_pv,song_id,month_part from sss ORDER BY play_pv desc), sssss as (select month,ROW_NUMBER() over (partition by month order by play_pv desc, song_id asc) as ranking, song_name, play_pv,month_part from ssss order by month_part) select month,ranking,song_name,play_pv from sssss where ranking in (1,2,3) order by month_part,ranking ;