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

with t as( 
        select *,
            timestampdiff(second,start_time,submit_time) as delta_t,
            row_number() over(partition by exam_id
                     order by  timestampdiff(second,start_time,submit_time) ) ranking_min,     
            row_number() over(partition by exam_id
                         order by  timestampdiff(second,start_time,submit_time) desc) ranking_max
        from exam_record
        where submit_time is not null
)

select ei.exam_id,duration,release_time
from examination_info ei
join (
    select exam_id,
           delta_t 
    from t
    where ranking_min = 2
) min_t
using(exam_id)
join(
    select exam_id,
           delta_t 
    from t
    where ranking_max = 2
) max_t
using(exam_id)
where (max_t.delta_t - min_t.delta_t) > duration*60/2
order by ei.exam_id desc