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

# 先找到每一个试卷作答用时第二快和第二慢的用时之差
with t1 as (
    select exam_id, timestampdiff(minute, start_time, submit_time) as total_time, 
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as ranking1,
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as ranking2
    from exam_record
    where submit_time is not null
    and exam_id in (select exam_id from exam_record where submit_time is not null group by exam_id having count(*) >= 3)
),# 第二慢用时
t2 as (
    select exam_id, total_time as slow
    from t1
    where ranking1 = 2
),# 第二快的用时
t3 as (
    select exam_id, total_time as fast
    from t1
    where ranking2 = 2
),
t4 as (
    select b.exam_id, c.duration, c.release_time, 
    (case when b.duration1 >= (c.duration / 2) then 'Yes' else 'No' end) as result
    from examination_info as c
    right join (
        select t2.exam_id, slow - fast as duration1
        from t2
        left join t3
        on t2.exam_id = t3.exam_id
    ) as b
    on b.exam_id = c.exam_id
)
select exam_id, duration, release_time
from t4
where result = 'Yes'
order by exam_id desc;