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

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 exam_id,duration,release_time from t 
group by exam_id
having (min(if(slow_record=2,time_record,null))-min(if(fast_record=2,time_record,null))) > duration/2
order by exam_id desc