这道题简单在从始至终不用group by

WITH a AS(
    SELECT
        exam_id,
        timestampdiff(second,start_time, submit_time) as finish,
        DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY timestampdiff(second,start_time, submit_time) DESC)  as fastrank,
        DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY timestampdiff(second,start_time, submit_time)) as slowrank
    FROM
        exam_record
),

a1 AS(
    SELECT
        exam_id,
        finish as finish1
    FROM
        a
    WHERE
        fastrank = 2
),

a2 AS(
    SELECT
        exam_id,
        finish as finish2
    FROM
        a
    WHERE
        slowrank = 2
)

SELECT
    exam_id,
    duration,
    release_time
FROM
    a1
JOIN 
    a2 USING(exam_id)
JOIN
    examination_info USING(exam_id)
WHERE
    abs(finish1 - finish2) > duration*60/2
ORDER BY
    exam_id DESC