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

with t21 as (
select
ei.tag,
year(er.start_time) as start_year,
count(er.score) as exam_cnt,
rank() over(order by count(er.score) desc) as exam_cnt_rank
from examination_info ei
inner join exam_record er
on ei.exam_id=er.exam_id
where date_format(er.start_time,'%Y%m') between '202101' and '202106'
group by ei.tag,year(er.start_time)
having count(er.score)!=0)
,t20 as (
select
ei.tag,
year(er.start_time) as start_year,
count(er.score) as exam_cnt,
rank() over(order by count(er.score) desc) as exam_cnt_rank
from examination_info ei
inner join exam_record er
on ei.exam_id=er.exam_id
where date_format(er.start_time,'%Y%m') between '202001' and '202006'
group by ei.tag,year(er.start_time)
having count(er.score)!=0)
select
    t21.tag,
    t20.exam_cnt as exam_cnt_20,
    t21.exam_cnt as exam_cnt_21,
    concat(round((t21.exam_cnt-t20.exam_cnt)/t20.exam_cnt*100,1),'%') growth_rate,
    t20.exam_cnt_rank as exam_cnt_rank_20,
    t21.exam_cnt_rank  as exam_cnt_rank_21,
    cast(t21.exam_cnt_rank as signed) - cast(t20.exam_cnt_rank as signed)  as rank_delta
from t21 inner join t20
where t21.tag=t20.tag
order by growth_rate desc ,exam_cnt_rank_21 desc ;