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

select t1.tag,
    exam_cnt_20,exam_cnt_21,
    concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate,
    rank_20 as exam_cnt_rank_20,
    rank_21 as exam_cnt_rank_21,
    cast(rank_21 as signed)-cast(rank_20 as signed) as rank_delta 
from (
    select tag,exam_cnt_20,
        rank() over (order by exam_cnt_20 desc) as rank_20 
    from (
        select tag,count(submit_time) as exam_cnt_20
        from examination_info as ei,exam_record as er
        where er.exam_id=ei.exam_id
            and year(start_time)=2020
            and submit_time is not null
            and month(start_time)<=6
        group by tag
    ) as t1_1
) as t1
join (
    select tag,exam_cnt_21,
        rank() over (order by exam_cnt_21 desc) as rank_21
    from (
        select tag,count(submit_time) as exam_cnt_21
        from examination_info as ei,exam_record as er
        where er.exam_id=ei.exam_id
            and year(start_time)=2021 
            and submit_time is not null
            and month(start_time)<=6
        group by tag
    ) as t2_1
) as t2
on t1.tag=t2.tag
group by tag
order by growth_rate desc,exam_cnt_rank_21 desc;