题解 | #连续两次作答试卷的最大时间窗#

select aa.tag,
      t1.exam_cnt_20,
      t2.exam_cnt_21,
      concat(round((t2.exam_cnt_21 - t1.exam_cnt_20) / t1.exam_cnt_20 * 100, 1), '%') as growth_rate,
      rank1,
      rank2,
      # 注意一般数据库默认都是unsigned, 是不能出现负数的, 可用cast(字段 as signed)即可
      cast(rank2 as SIGNED) - cast(rank1 as SIGNED) as exam_cnt_rank
  from (
      select exam_id,
          count(submit_time) exam_cnt_20,
          # 备注: 这里是不能用partition的, 因为是根据分组之后的exam_id进行的
          rank() over(order by count(submit_time) desc) as rank1
      from exam_record
      where year(start_time)=2020
      and month(start_time) <= 6
      group by exam_id
      having exam_cnt_20 >= 1
  ) t1
  join (
      select exam_id,
          count(submit_time) exam_cnt_21,
          rank() over(order by count(submit_time) desc) as rank2
      from exam_record
      where year(start_time)=2021
      and month(start_time) <= 6
      group by exam_id
      having exam_cnt_21 >= 1
  ) t2 on t1.exam_id=t2.exam_id
  join examination_info aa on t1.exam_id=aa.exam_id
  order by growth_rate desc, t2.exam_cnt_21