题解 | #分别满足两个活动的人#

用的最原始的方法，即得到一张表，含有exam_id、duration、release_time、第二长和第二短的作答时间，然后只需计算两者之差与duration的一半比较即可。

select exam_id,duration,release_time
/*开始构建表格*/
from (
    select exam_id,release_time,duration from examination_info
) A 
left join 
(
    select exam_id,du
    from (
        select exam_id,timestampdiff(minute,start_time,submit_time) du,row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) rn_1
        from exam_record ) X  
        where rn_1 = 2
) B using(exam_id)
left join
(
    select exam_id, du_1
    from (
        select exam_id,timestampdiff(minute,start_time,submit_time) du_1,row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) rn_2
        from exam_record ) Y 
        where rn_2 = 2
) C using(exam_id)
/*完成构建表格*/
where du_1 - du > 0.5*duration
order by exam_id desc