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

select exam_id,duration,release_time
from
(select exam_id,
    sum(case when rnk1=2 then ans_time else 0 end ) max_ans,
    sum(case when rnk2=2 then ans_time else 0 end ) min_ans
from 
    (select 
        exam_id,uid,
        timestampdiff(MINUTE,start_time,submit_time) as ans_time,
        row_number() over(partition by exam_id order by timestampdiff(MINUTE,start_time,submit_time) desc) as rnk1,
        row_number() over(partition by exam_id order by timestampdiff(MINUTE,start_time,submit_time) ) as rnk2
    from exam_record
    where submit_time is not null) tb1
group by exam_id) tb2
join examination_info using(exam_id)
where (max_ans - min_ans) >= (duration / 2)
order by exam_id desc