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

搬砖

select tag,exam_cnt_20,exam_cnt_21,
concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*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 
(select tag,
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 exam_cnt_rank end) as exam_cnt_rank_20,
sum(case when start_year=2021 then exam_cnt_rank end) as exam_cnt_rank_21
from
(select
tag,start_year,exam_cnt,
rank() over(partition by start_year order by exam_cnt desc) as exam_cnt_rank
from 
(select tag,start_year,
count(*) as exam_cnt
from 
(select tag,year(submit_time) as start_year
from exam_record e_r join examination_info e_i
on e_r.exam_id=e_i.exam_id
where submit_time is not null and year(submit_time) between 2020 and 2021 
and month(submit_time)<6)a
group by tag,start_year
)b)c
group by tag)d
where exam_cnt_21 is not null and exam_cnt_20 is not null
order by growth_rate desc,exam_cnt_rank_21 desc;