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

select tag,exam_cnt_20,exam_cnt_21,growth_rate,
  exam_cnt_rank_20,exam_cnt_rank_21,
  cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta
from
   (select *,
    rank()over(order by exam_cnt_20 desc) exam_cnt_rank_20,
    rank()over(order by exam_cnt_21 desc) exam_cnt_rank_21,
    concat(round(((exam_cnt_21 - exam_cnt_20)/exam_cnt_20) * 100,1) , '%' ) as growth_rate
    from
     (select tag,
     sum(if(date_format(submit_time,'%Y%m') >= 202101 and date_format(submit_time,'%Y%m') <= 202106 ,1,0) ) as exam_cnt_21,
     sum(if(date_format(submit_time,'%Y%m') >= 202001 and date_format(submit_time,'%Y%m') <= 202006 ,1,0) ) as exam_cnt_20
     from exam_record 
     right join examination_info using(exam_id)
     group by tag
     )t1
   )t2
where exam_cnt_21 <> 0 and exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc