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

WITH
    finish_2020 as (
        select
            '2020' as start_year,
            ei.tag,
            count(er.submit_time) as exam_cnt
        from
            exam_record er 
        left join
            examination_info ei on er.exam_id = ei.exam_id
        where 
            year(er.submit_time) = 2020 and month(er.submit_time) < 7 and er.submit_time is not null
        group by 
            1,2
    ),
    finish_2021 as (
        select
            '2021' as start_year,
            ei.tag,
            count(er.submit_time) as exam_cnt
        from
            exam_record er 
        left join
            examination_info ei on er.exam_id = ei.exam_id
        where 
            year(er.submit_time) = 2021 and month(er.submit_time) < 7 and er.submit_time is not null
        group by 
            1,2
    ),
    finish_2020_rank as (
        select
            tag,
            start_year,
            exam_cnt,
            rank() over(order by exam_cnt desc) as exam_cnt_rank
        from
            finish_2020
    ),
    finish_2021_rank as (
        select
            tag,
            start_year,
            exam_cnt,
            rank() over(order by exam_cnt desc) as exam_cnt_rank
        from
            finish_2021
    )

select
    f2.tag,
    f1.exam_cnt as exam_cnt_20,
    f2.exam_cnt as exam_cnt_21,
    concat(round((f2.exam_cnt - f1.exam_cnt) / f1.exam_cnt * 100 ,1),'%') as growth_rate,
    f1.exam_cnt_rank as exam_cnt_rank_20,
    f2.exam_cnt_rank as exam_cnt_rank_21,
    cast(f2.exam_cnt_rank as signed) - cast(f1.exam_cnt_rank as signed) as rank_delta
from
    finish_2020_rank f1
join
    finish_2021_rank f2 on f1.tag = f2.tag
order by 
    growth_rate desc,exam_cnt_rank_21 desc