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

select t1.tag,2020_cnt,2021_cnt,concat(round((2021_cnt-2020_cnt)/2020_cnt*100,1),'%') 'growth_rate',2020_rank,2021_rank, CAST(2021_rank as SIGNED) - CAST(2020_rank AS SIGNED) 'rank_delta'
from((select tag, count(score) '2021_cnt',rank()over(order by count(score) desc) '2021_rank'
from exam_record r left join examination_info i on r.exam_id = i.exam_id
where date_format(start_time,'%Y%m') between '202101' and '202106'
and submit_time is not null
group by tag) t1 inner join 
(
select tag, count(score) '2020_cnt',rank()over(order by count(score) desc) '2020_rank'
from exam_record r left join examination_info i on r.exam_id = i.exam_id
where date_format(start_time,'%Y%m') between '202001' and '202006'
and submit_time is not null
group by tag) t2 on t1.tag = t2.tag)
order by growth_rate desc,rank_delta desc