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

with table1 as(
    select exam_id,
    duration,
    timestampdiff(minute,start_time,submit_time) as time,release_time
    from exam_record left join examination_info using(exam_id)
    where submit_time is not null
),
table2 as (
    select exam_id,time,
    row_number() over(partition by exam_id order by time desc) as desc_time,
    row_number() over(partition by exam_id order by time) as asc_time,
    duration , release_time
    from table1
),
table3 as (
select exam_id,duration,release_time, 
sum(case when desc_time = 2 then time 
         when asc_time = 2 then -time
         else 0 
         end) as sum_time
from table2
group by exam_id,duration,release_time
having sum_time >= duration*0.5
order by exam_id desc
)
select exam_id,duration,release_time
from table3