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

with N as(select distinct T.exam_id,D
 from(
    select exam_record.exam_id,timestampdiff(minute,start_time,submit_time) as D,dense_rank()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as R
    from exam_record
) as T
where R=2
group by exam_id,D)

,
 n as(select distinct t.exam_id,d
 from(

    select exam_record.exam_id,timestampdiff(minute,start_time,submit_time) as d,dense_rank()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) as r
    from exam_record
) as t
where r=2)
select a.exam_id,duration,release_time
from (
    select examination_info.exam_id,duration,release_time,N.D-n.d as dt
    from examination_info
    inner join N
    on examination_info.exam_id=N.exam_id
    inner join n
    on N.exam_id=n.exam_id
    group by N.exam_id,N.D,n.d
    order by N.exam_id) as a
where dt>=duration/2
order by  a.exam_id desc