题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
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