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

with temp as(
select 
    t1.exam_id
    ,duration
    ,release_time
    ,timestampdiff(minute,start_time,submit_time) diff
    ,row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) diff_rank1
    ,row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) diff_rank2
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
where submit_time is not null
)
select exam_id,duration,release_time from(
select 
    exam_id
    ,duration
    ,release_time
    ,sum(case when diff_rank1 = 2 then diff when diff_rank2 = 2 then -diff else 0 end) sub
from temp 
group by exam_id)a
where sub >= duration/2 
order by exam_id desc