题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
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