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

select tag,exam_cnt_20,exam_cnt_21,concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') rat,exam_cnt_rank_20,exam_cnt_rank_21,exam_cnt_rank_21-exam_cnt_rank_20
from
(select tag,sum(if(yr=2020,cnt,0)) exam_cnt_20,sum(if(yr=2021,cnt,0)) exam_cnt_21,
max(if(yr=2020,rk,0)) exam_cnt_rank_20,max(if(yr=2021,rk,0)) exam_cnt_rank_21
from
(select tag,yr,cnt,rank()over(partition by yr order by cnt desc) rk
from
(select tag,yr,count(yr) cnt
from
(select tag,year(start_time) yr
from
examination_info e1
join
exam_record e2
on
e1.exam_id=e2.exam_id
where
date(submit_time) between '2021-01-01' and '2021-06-30'
or 
date(submit_time) between '2020-01-01' and '2020-06-30'
and score is not null) as t1
group by tag,yr) as t2) as t3
group by tag) as t4
where exam_cnt_20!=0 and exam_cnt_21!=0
order by rat desc,exam_cnt_rank_21 desc