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

select
id exem_id
,max(duration) duration
,max(release_time) release_time
from(
select
ei.exam_id id
,row_number()over(partition by ei.exam_id order by (submit_time-start_time)) rk1
,row_number()over(partition by ei.exam_id order by (submit_time-start_time) desc) rk2
,submit_time-start_time len
,duration
,release_time
from examination_info ei join exam_record er on ei.exam_id=er.exam_id
where score is not null) as a
group by id
having (sum(if(rk2=2,len,0))-sum(if(rk1=2,len,0)))>0.5*max(duration)
order by exem_id desc