题解 | #每个6/7级用户活跃情况#

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降序排序。