题解 | #试卷完成数同比2020年的增长率及排名变化#

试卷完成数同比2020年的增长率及排名变化

http://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b

题目逻辑不难,但需要建立新表再连接,这种类型的题目建议先用with t as 建立新表再计算需要的指标,这样思路更清晰。

1:分别建立2020和2021上半年各类型的试卷的完成数以及排名表t1和t2,排名使用rank()over(partition by )即可。

2:连接t1和t2,求出所需指标即可

特别注意点 排名差值相减时出现负数会报错,因为rank函数没有符号。 因此考虑使用cast()函数将排名转化为数字再相减

with t1 as 
(select tag,count(submit_time) as com,
rank()over(order by count(submit_time) desc ) as rk
from exam_record as b join examination_info as a on a.exam_id=b.exam_id
where submit_time between '2020-01-01' and '2020-06-30'
group by tag ),
t2 as 
(select tag,count(submit_time) as co,
rank()over(order by count(submit_time) desc ) as rn
from exam_record as b join examination_info as a on a.exam_id=b.exam_id
where submit_time between '2021-01-01' and '2021-06-30'
group by tag )

select t1.tag,t1.com,t2.co,concat(round((co-com)/com*100,1),'%') as gr,
rk,rn,cast(rn as signed)-cast(rk as signed)
from t1 join t2 on t1.tag=t2.tag
order by gr desc,rk desc



全部评论

相关推荐

06-11 17:39
门头沟学院 Java
小呆呆的大鼻涕:卧槽,用户彻底怒了
点赞 评论 收藏
分享
Gaynes:查看图片
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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