# 思路：
# 1、计算2021上半年各类试卷的做完次数和排名（用where过滤，用rank排名）
# 2、计算2020年上半年各类试卷的做完次数和排名
# 3、用inner join 将两年连接起来，计算做完次数和排名变化
select t2.tag,t3.exam_cnt_20,t2.exam_cnt_21
,concat(round((t2.exam_cnt_21/t3.exam_cnt_20-1)*100,1),'%') as growth_rate
,t3.exam_cnt_rank_20,t2.exam_cnt_rank_21
,cast(t2.exam_cnt_rank_21 as signed)-cast(t3.exam_cnt_rank_20 as signed) as rank_delta from 
(select t1.tag,t1.exam_cnt_21
,rank() over(order by t1.exam_cnt_21 desc) as exam_cnt_rank_21 
from (
select e1.tag
,count(e2.submit_time) as exam_cnt_21 
from exam_record as e2 left join examination_info as e1 
on e2.exam_id=e1.exam_id 
where e2.start_time between '2021-01-01' and '2021-06-30' 
group by e1.tag 
) t1 ) t2
 inner join (
select t1.tag,t1.exam_cnt_20
,rank() over(order by t1.exam_cnt_20 desc) as exam_cnt_rank_20 
from (
select e1.tag
,count(e2.submit_time) as exam_cnt_20 
from exam_record as e2 left join examination_info as e1 
on e2.exam_id=e1.exam_id 
where e2.start_time between '2020-01-01' and '2020-06-30' 
group by e1.tag 
) t1  ) t3 
on t2.tag=t3.tag
order by growth_rate desc,exam_cnt_rank_21 desc