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

with info_20 as(
select 
tag,year(start_time) as start_year_20,
count(submit_time)  as exam_cnt_20,
rank() over( order by count(submit_time) desc) as exam_cnt_rank_20

from examination_info ei
join exam_record er on ei.exam_id = er.exam_id
#上半年
where substr(start_time,6,2) not in ('07','08','09','10','11','12')
and submit_time is not null
and year(start_time) =2020

group by tag,year(start_time)),

info_21 as(
    select 
        tag,year(start_time) as start_year_21,
        count(submit_time)  as exam_cnt_21,
        rank() over( order by count(submit_time) desc) as exam_cnt_rank_21

        from examination_info ei
        join exam_record er on ei.exam_id = er.exam_id
        #上半年
        where substr(start_time,6,2) not in ('07','08','09','10','11','12')
        and submit_time is not null
        and year(start_time) =2021

        group by tag,year(start_time)
)

select info_21.tag,exam_cnt_20,exam_cnt_21,
concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),'%')as 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 info_20
left join info_21 on info_20.tag = info_21.tag
where info_21.tag<>''
order by growth_rate desc,exam_cnt_rank_21 desc

我的方法是先分别写2020年、2021年的tag, start_year_2X,exam_cnt_2X,exam_cnt_rank_2X

再通过主查询连接2张表，求出growth_rate和排名差，注意要cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed)