题解 | 每个月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
;

全部评论

相关推荐

昨天 11:42
江西农业大学 C++
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
07-04 14:23
steelhead:你回的有问题,让人感觉你就是来学习的
点赞 评论 收藏
分享
06-05 19:46
已编辑
武汉大学 后端
点赞 评论 收藏
分享
05-16 11:16
已编辑
东华理工大学 Java
牛客73769814...:盲猜几十人小公司,庙小妖风大,咋不叫她去4️⃣呢😁
牛客创作赏金赛
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
昨天 12:02
ssob上原来真有BOSS啊
硫蛋蛋:这种也是打工的,只不是是给写字楼房东打工
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务