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

SELECT
    exam_id,
    duration,
    release_time 
FROM
    (
    SELECT
        exam_id,
        duration,
        release_time,
        fin_time,
        TIMEDIFF(
            fin_time,
        lag( fin_time, 1 ) over ( PARTITION BY exam_id ORDER BY fin_time )) AS con,
        0.5 * duration AS com 
    FROM
        (
        SELECT
            info.exam_id,
            info.duration,
            info.release_time,
            timediff( re.submit_time, re.start_time ) AS fin_time,
            dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) ) AS h_ranking,
            dense_rank() over ( PARTITION BY info.exam_id ORDER BY timediff( re.submit_time, re.start_time ) DESC ) AS l_ranking 
        FROM
            exam_record re
            JOIN examination_info info ON re.exam_id = info.exam_id 
        WHERE
            re.submit_time IS NOT NULL 
        ) a 
    WHERE
        h_ranking = 2 
        OR l_ranking = 2 
    ORDER BY
        exam_id,
        fin_time 
    ) c 
WHERE
    MINUTE(con)+SECOND(con)/60 > com 
ORDER BY
    exam_id DESC;