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

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

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

思路:
1. 使用窗口函数为每个exam_id组中的每个试卷记录赋予用时排名;分为正向用时和反向用时;计算每份试卷记录的答题用时列time_diff;
2. 筛选出正向用时或反向用时排名为2的试卷记录;
3. 按exam_id分组,筛选组内最大time_diff - 最小的time_diff > 0.5 * duration的组;
4. 完成查询, easy.

select
    exam_id,duration,release_time
from
    (select
        exam_id, 
        timestampdiff(minute, start_time, submit_time) as time_diff, 
        row_number() 
        over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as t_ranking,
        row_number() 
        over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) asc) as p_ranking
    from exam_record) t_diff left join examination_info using(exam_id)
where t_ranking=2 or p_ranking=2
group by exam_id
having max(time_diff)-min(time_diff) > 0.5*min(duration)
order by exam_id desc

全部评论

相关推荐

头像
02-26 13:58
门头沟学院 Java
北城_阿亮:把八股背一背,包装一下实习经历项目经历,要是有心思考证就考一考,然后把别人的项目爬到自己github上,包装到简历里,什么三个月?一个月!
点赞 评论 收藏
分享
北斗导航Compass低仿版:没必要写这么多东西,还是尽量浓缩成一页,自我评价,git和cursor Trae这些都可以去掉。实习经历的描述最好根据star法则改一下,别这么直白
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务