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

1. 统计所有试卷按tag 分类得到的2020年总作答数和2021年总作答数；然后通过2020年和2021年的作答总数分别得到各自年份所在试卷内部的排名，以及增长率的变化，第三步的得到排名的变化，并记得过滤2020年和2021年作答总数均大于0 的试卷条件过滤，最后按照增长率和2021年排名降序排列。



# 统计所有年份的 上半年各类试卷的做完次数与做完的排名 
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) rank_delta 
    from (
    select tag, 
           exam_cnt_21 ,
           exam_cnt_20 ,
           concat(round((exam_cnt_21-exam_cnt_20)*100.0/exam_cnt_20,1),'%') growth_rate , 
           rank() over(order by exam_cnt_20 desc) exam_cnt_rank_20 ,
           rank() over(order by exam_cnt_21 desc) exam_cnt_rank_21 
      from (
    select EI.tag,
           count(if(year(submit_time)=2021,1,null)) exam_cnt_21,
           count(if(year(submit_time)=2020,1,null)) exam_cnt_20 
        from exam_record ER join examination_info EI on ER.exam_id = EI.exam_id 
       where year(submit_time) in (2020,2021) and month(submit_time) < 7 
       group by EI.tag 
       ) A 
    ) B 
    where B.exam_cnt_20 >0 and B.exam_cnt_21 > 0 
    order by growth_rate desc,exam_cnt_rank_21 desc;