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

思路：
1. 使用窗口函数为每个exam_id组中的每个试卷记录赋予用时排名;分为正向用时和反向用时;计算每份试卷记录的答题用时列time_diff;
2. 筛选出正向用时或反向用时排名为2的试卷记录;
3. 按exam_id分组,筛选组内最大time_diff - 最小的time_diff > 0.5 * duration的组;
4. 完成查询, easy.

select
    exam_id,duration,release_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) as t_ranking,
        row_number() 
        over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) asc) as p_ranking
    from exam_record) t_diff left join examination_info using(exam_id)
where t_ranking=2 or p_ranking=2
group by exam_id
having max(time_diff)-min(time_diff) > 0.5*min(duration)
order by exam_id desc