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

with t1 as (
    select exam_id,
    timestampdiff(minute,start_time,submit_time) time_used
    from exam_record
    ),
t2 as(
    select exam_id,
    time_used,
    row_number() over(partition by exam_id order by time_used asc) fast_rank,
    row_number() over(partition by exam_id order by time_used desc) slow_rank
    from t1
    ),
t3 as(
    select exam_id,
    max(case when fast_rank=2 then time_used end) second_fastest,
    max(case when slow_rank=2 then time_used end) second_slowest,
    max(case when slow_rank=2 then time_used end)-max(case when fast_rank=2 then time_used end) time_diff
    from t2
    group by exam_id
    having second_fastest is not null and second_slowest is not null
)   
select exam_id,
duration,
release_time
from examination_info ei
join t3 using(exam_id)
where t3.time_diff >= (ei.duration/2)
order by ei.exam_id desc