题解 | #每个6/7级用户活跃情况#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
select
e.exam_id,
e.duration,
release_time
from
(
select
t.exam_id,
max(
case
when fastt = 2 then timediff
end
) as fast_2,
max(
case
when sloww = 2 then timediff
end
) as slow_2
from
(
select
*,
timestampdiff(second, start_time, submit_time) as timediff,
dense_rank() over(
partition by exam_id
order by
timestampdiff(second, start_time, submit_time) desc
) as fastt,
dense_rank() over(
partition by exam_id
order by
timestampdiff(second, start_time, submit_time)
) as sloww
from
exam_record
where
submit_time is not null
) as t
group by
t.exam_id
) as t
join examination_info as e using(exam_id)
where
(fast_2 - slow_2) >= 0.5 * 60 * duration
order by
exam_id desc
用了两次子查询: 注意本题是在所有试卷的第二快和第二慢完成答题的人数中找出每类试卷的两个用时之差大于0.5duration的,而不是第二快和第二慢用时之差; 首先在recored表中把用时之差、用时最快排名、最慢排名组成新表; 其次筛选出rank=2的第二快和第二慢的时间; 最后筛选出作差大于0.5duration并链接试卷信息表按exam_id降序排序。