1.row_number

select exam_id, duration, release_time from ( SELECT er.exam_id, uid, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) as spend_time, ROW_NUMBER() over( partition by exam_id order by TIMESTAMPDIFF(MINUTE, start_time, submit_time) desc ) as rank1, ROW_NUMBER() over( partition by exam_id order by TIMESTAMPDIFF(MINUTE, start_time, submit_time) asc ) as rank2 from exam_record as er left join examination_info as ei on er.exam_id = ei.exam_id where submit_time is not null ) as a group by exam_id having sum( case when rank1 = 2 then spend_time when rank2 = 2 then - spend_time else 0 end ) * 2 >= duration order by exam_id desc

2.nth_value

SELECT distinct exam_id, duration, release_time FROM ( SELECT er.exam_id, uid, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) as spend_time, nth_value(TIMESTAMPDIFF(MINUTE, start_time, submit_time), 2) over( PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC ) as max2_time_took, nth_value(TIMESTAMPDIFF(MINUTE, start_time, submit_time), 2) over( PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) asc ) as min2_time_took FROM exam_record as er left JOIN examination_info ei on er.exam_id = ei.exam_id WHERE submit_time IS NOT NULL ) as a WHERE max2_time_took - min2_time_took >= duration / 2 ORDER BY exam_id DESC