思路分析：

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序：

• 找到第二快和第二慢；
• 筛选出用时之差>试卷时长的一半的试卷
• 提取这些试卷的试卷信息并按试卷ID排序

具体分析——一步一表：

• 找到第二快和第二慢——应用两次窗口函数

row_number() over (partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as rn1,
row_number() over (partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) as rn2

• 计算出用时之差

由于对两列指定数据进行加和比较困难，不如把每类的若干条数据都进行加和，但设置条件：

sum(case when rn1=2 then duration0 when rn2=2 then -duration0 else 0 end)

此处注意，由于是对每类进行加和，最后要+group by

• 筛选并提取试卷信息

select exam_id,duration,release_time
……
where sub>30*duration

也就是把上一步的筛选工作放在这一步用where语句执行

提交答案

select exam_id,duration,release_time
from
    (select exam_id,duration,release_time,
    sum(case when rn1=2 then duration0 when rn2=2 then -duration0 else 0 end)
    as sub
    from
        (select uid,er.exam_id,duration,release_time,
        timestampdiff(second,start_time,submit_time) as duration0,
        row_number() over (partition by exam_id 
        order by timestampdiff(second,start_time,submit_time) desc) as rn1,
         row_number() over (partition by exam_id 
        order by timestampdiff(second,start_time,submit_time) asc) as rn2
         from exam_record as er
         join examination_info as ei
         on er.exam_id=ei.exam_id 
         where submit_time is not null
        ) as q1
     group by exam_id
    ) as q2
where sub>30*duration
order by exam_id desc