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

with t as (select ei.exam_id as exam_id,duration,
timestampdiff(second,start_time,submit_time)/60 as time_record,
rank()over(partition by exam_id order by timestampdiff(second,start_time,submit_time)/60) as fast_record,
rank()over(partition by exam_id order by timestampdiff(second,start_time,submit_time)/60 desc) as slow_record,
release_time
from examination_info ei
join exam_record using(exam_id)
where score is not null)

select t1.exam_id,t1.duration,t1.release_time from 
(select * from t 
where fast_record = 2) t1
join
(select * from t 
where slow_record = 2) t2
using(exam_id,duration,release_time) 
where t2.time_record-t1.time_record>t1.duration/2
order by exam_id desc