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

 -- 找到第二快和第二慢用时之差大于等于试卷时长的一半的试卷信息，按试卷ID降序排序。
with
    desc_asc_rk as (
        select
            exam_id,
            duration,
            release_time,
            timestampdiff(second, start_time, submit_time) / 60 as took_time,
            rank() over (
                partition by
                    exam_id
                order by
                    timestampdiff(second, start_time, submit_time) desc
            ) as desc_rk,
            rank() over (
                partition by
                    exam_id
                order by
                    timestampdiff(second, start_time, submit_time) asc
            ) as asc_rk
        from
            examination_info
            join exam_record using (exam_id)
        where
            submit_time is not null
    )
select distinct
    exam_id,
    duration,
    release_time
from
    desc_asc_rk
where
    exam_id in (
        select
            exam_id
        from
            desc_asc_rk
        where
            desc_rk = 2
            or asc_rk = 2
        group by
            exam_id
        having
            (max(took_time) - min(took_time)) >= 0.5 * max(duration)
    )
order by
    exam_id desc