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

select
    exam_id,
    duration,
    release_time
from
    (
        select
            t1.exam_id,
            e.duration,
            e.release_time,
            sum(
                case
                    when t1.rank_desc = 2 then t1.time_diff
                    when t1.rank_asc = 2 then - t1.time_diff
                    else 0
                end
            ) as sum_time
        from
            (
                select
                    exam_id,
                    timestampdiff (minute, start_time, submit_time) as time_diff,
                    row_number() over (
                        partition by
                            exam_id
                        order by
                            timestampdiff (minute, start_time, submit_time) desc
                    ) rank_desc,
                    row_number() over (
                        partition by
                            exam_id
                        order by
                            timestampdiff (minute, start_time, submit_time) asc
                    ) rank_asc
                from
                    exam_record
                where
                    submit_time is not null
            ) t1
            left join examination_info e on t1.exam_id = e.exam_id
        group by
            t1.exam_id
    ) t2
where
    sum_time >= duration / 2
order by
    exam_id desc