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

select 
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(format((exam_cnt_21 - exam_cnt_20)*1.0/exam_cnt_20*100,1),'%') as growth_rate,
    exam_cnt_rank_20,
    exam_cnt_rank_21,
    exam_cnt_rank_21 - exam_cnt_rank_20 as rank_delta
from 
(
    select 
        exam_id,
        sum(case when start_year=2020 then exam_cnt end) as exam_cnt_20,
        sum(case when start_year=2021 then exam_cnt end) as exam_cnt_21,
        sum(case when start_year=2020 then rk end) as exam_cnt_rank_20,
        sum(case when start_year=2021 then rk end) as exam_cnt_rank_21
    from 
    (
        select 
            exam_id,start_year,exam_cnt,rank() over(partition by start_year order by exam_cnt desc) as rk
        from 
        (
            select 
                exam_id,year(start_time) as start_year,count(submit_time) as exam_cnt
            from exam_record where  (date_format(start_time,'%Y%m') >= '202001' and date_format(start_time,'%Y%m') <= '202006')
            or (date_format(start_time,'%Y%m') >= '202101' and date_format(start_time,'%Y%m') <= '202106')
            group by exam_id,year(start_time) having count(submit_time) > 0
        ) t1 
    ) t2 group by exam_id having count(1) >= 2
) t3 join examination_info ei on t3.exam_id=ei.exam_id
order by growth_rate desc,exam_cnt_rank_21 desc