题解 | 试卷完成数同比2020年的增长率及排名变化

SELECT  
  tag,
  exam_2020cnt as exam_cnt_20,
  exam_2021cnt as exam_cnt_21,
  CONCAT(ROUND(100*(exam_2021cnt - exam_2020cnt) / exam_2020cnt,1),'%') as growth_rate,
   rk2020 as exam_cnt_rank_20,
    rk2021 as exam_cnt_rank_21,
     CAST(rk2021 as SIGNED) - CAST(rk2020 AS SIGNED) rank_delta
FROM
(
SELECT 
  *,
  RANK() over(ORDER BY exam_2020cnt DESC) rk2020,
  RANK() over(ORDER BY exam_2021cnt DESC) rk2021
FROM
(
  SELECT 
  tag,
  count(if(er.start_time BETWEEN '2020-01-01' and '2020-05-31',1,null)) as exam_2020cnt,
  count(if(er.start_time BETWEEN '2021-01-01' and '2021-05-31',1,null)) as exam_2021cnt
  FROM exam_record er 
  LEFT JOIN examination_info ei
  USING(exam_id)
  WHERE er.submit_time is not null
  GROUP BY ei.tag
) t
) t2
WHERE exam_2020cnt!=0 and exam_2021cnt!=0
ORDER BY growth_rate DESC,  exam_cnt_rank_21 DESC