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

学到了一个新聚合函数nth_value()

with temp as
(select exam_id, 
nth_value(timestampdiff(second, start_time, submit_time), 2) 
 over(partition by exam_id
      order by timestampdiff(second, start_time, submit_time) desc) - 
nth_value(timestampdiff(second, start_time, submit_time), 2) 
 over(partition by exam_id
      order by timestampdiff(second, start_time, submit_time)) as time_diff
from exam_record)

select distinct exam_id, duration, release_time FROM
examination_info join temp using(exam_id)
where time_diff > duration * 60 / 2

order by exam_id desc