题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

第二快/慢用时之差大于试卷时长一半的试卷

https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

WITH t as(
SELECT exam_id,
TIMESTAMPDIFF(SECOND,start_time,submit_time) AS use_time,
ROW_NUMBER()OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) ASC) AS fast_rank,
ROW_NUMBER()OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) DESC) AS slow_rank
FROM exam_record 
WHERE submit_time IS NOT NULL   
),
t1 as(
SELECT t.exam_id,t.use_time,duration,release_time                      
FROM t
JOIN examination_info 
ON t.exam_id =examination_info.exam_id 
WHERE fast_rank =2 
),
t2 as(
SELECT t.exam_id,t.use_time,duration,release_time                      
FROM t
JOIN examination_info 
ON t.exam_id =examination_info.exam_id 
WHERE slow_rank =2 
)
SELECT DISTINCT t1.exam_id,t1.duration,t1.release_time
FROM t1 JOIN t2
WHERE (t2.use_time - t1.use_time)*2 > t1.duration * 60
ORDER BY t1.exam_id DESC;

全部评论

相关推荐

Yki_:你要算时间成本呀,研究生两三年,博士三四年,加起来就五六年了,如果你本科去腾讯干五年,多领五年的年薪,加上公司内涨薪,可能到时候十五年总薪资也跟博士差不多
点赞 评论 收藏
分享
不对是145个人…嗯… 大家都没发现秋招提前批来了嘛..笑死我了
牛客39712426...:投了也是浪费时间,之前投米实习,除了浪费我时间写笔试题没有任何反馈,懒得投了
26届校招投递进展
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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