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

with t as (
select exam_id,sum(case when fast=2 then dt end) as k,sum(case when slow=2 then dt end) as m from(
select exam_id,dt,row_number() over(partition by exam_id order by dt ) as fast,row_number() over(partition by exam_id order by dt desc) as slow from(
select distinct exam_id,timestampdiff(second,start_time,submit_time) as dt from exam_record where score is not null order by 1) tmp) tp group by 1)

select exam_id,duration,release_time from t left join examination_info using(exam_id) where (m-k)/60>=duration/2 order by 1 desc