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

/*
 找到第二快和第二慢用时 之差
 大于试卷时长的一半的试卷信息，
 按试卷ID降序排序
 
 */
select
    exam_id,
    duration,
    release_time
from
    (
        select
            exam_id,
            max(
                case
                    when rn_slow = 2 then duration
                    else 0
                end
            ) - min(
                case
                    when rn_fast = 2 then duration
                    else 0
                end
            ) as res
        from
            (
                select
                    exam_id,
                    timestampdiff(second, start_time, submit_time) as duration,
                    row_number() over(
                        partition by exam_id
                        order by
                            timestampdiff(second, start_time, submit_time) desc
                    ) as rn_slow,
                    row_number() over(
                        partition by exam_id
                        order by
                            timestampdiff(second, start_time, submit_time)
                    ) as rn_fast
                from
                    exam_record
                where
                    submit_time is not null
            ) a
        where
            rn_slow = 2
            or rn_fast = 2
        group by
            exam_id
    ) t1
    inner join examination_info t2 using (exam_id)
where
    t1.res > (t2.duration * 60) / 2
order by
    exam_id desc;