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

with tiaojian as (
select
t.exam_id,
sum(case when t.m=2 then diff when t.d=2 then -diff end)/60 as pt
from(
select
exam_id,
timestampdiff(second,start_time,submit_time) as diff,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as m,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) asc) as d
from exam_record
) as t
group by t.exam_id
)


select
t.exam_id,
ef.duration,
ef.release_time
from tiaojian t left join examination_info ef on t.exam_id=ef.exam_id
where
t.pt>=ef.duration/2
order by t.exam_id desc