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

with t1 as (select
  exam_id,
  timestampdiff(minute,start_time ,submit_time)  as x1,         
  row_number() over (partition by exam_id  order by timestampdiff(minute,start_time ,submit_time) desc)
  as second_slow,
  row_number() over (partition by exam_id  order by timestampdiff(minute,start_time ,submit_time) )
  as second_qucik,
  duration,
  release_time        
from exam_record
join examination_info
using(exam_id)
where score is not null)

select exam_id,t2.duration,t2.release_time
from(
        select *
        from t1
        where second_slow=2 ) as t2
left join (
        select *
        from t1
        where second_qucik=2 ) as t3
using(exam_id)

where  (t2.x1-t3.x1)>=t2.duration/2
group by exam_id
order by exam_id desc