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

select
tag,
sum(case when start_year = 2020 then exam_cnt else 0 end) as exam_cnt_20,
sum(case when start_year = 2021 then exam_cnt else 0 end) as exam_cnt_21,
concat(round(((sum(case when start_year = 2021 then exam_cnt else 0 end)-sum(case when start_year = 2020 then exam_cnt else 0 end))/sum(case when start_year = 2020 then exam_cnt else 0 end))*100,1),'%') as growth_rate,

sum(case when start_year = 2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20,
sum(case when start_year = 2021 then exam_cnt_rank else 0 end) as exam_cnt_rank_21,
sum(case when start_year = 2021 then exam_cnt_rank else 0 end) - sum(case when start_year = 2020 then exam_cnt_rank else 0 end) as rank_delta

from

(
select 
info.tag,
date_format(rec.start_time, '%Y') as start_year,
count(rec.submit_time) as exam_cnt,
rank() over (partition by date_format(rec.start_time, '%Y') order by count(rec.submit_time) desc) as exam_cnt_rank
from exam_record rec
left join examination_info info 
on rec.exam_id = info.exam_id
where month(rec.start_time) <= 6
group by info.tag, date_format(rec.start_time, '%Y')
) t
group by tag
having exam_cnt_20 != 0 and exam_cnt_21 != 0

order by growth_rate desc, exam_cnt_rank_21 desc