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

select
    exam_id,
    duration,
    release_time
from
    (
        select
            exam_id,
            duration,
            release_time,
            作答用时 - lag (作答用时, 1) over (
                partition by
                    exam_id
                order by
                    作答用时 asc
            ) 做差结果
        from
            (
                select
                    ei.exam_id,
                    duration,
                    release_time,
                    timestampdiff (second, start_time, submit_time) / 60 作答用时,
                    rank() over (
                        partition by
                            ei.exam_id
                        order by
                            timestampdiff (second, start_time, submit_time) / 60 desc
                    ) 从大到小排名,
                    rank() over (
                        partition by
                            ei.exam_id
                        order by
                            timestampdiff (second, start_time, submit_time) / 60 asc
                    ) 从小到大排名
                from
                    test.examination_info ei
                    join test.exam_record er on ei.exam_id = er.exam_id
            ) a
        where
            a.从大到小排名 = 2
            or a.从小到大排名 = 2
    ) b
where
    做差结果 is not null
    and 做差结果 > duration / 2
order by
    exam_id desc