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

# 分别输出2020和2021的表，记录做完的次数和名次
# 连接两张表，join，同时计算增长率和排名变化


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
-- 2020的表
(select tag,2020 as start_year,count(submit_time)as exam_cnt_20,
rank()over(order by count(submit_time) desc) as exam_cnt_rank_20
from exam_record join examination_info using(exam_id)
where year(submit_time)=2020 and month(submit_time)<=6
group by tag) 2020_table

join 
-- 2021的表
(select tag,2021 as start_year,count(submit_time)as exam_cnt_21,
rank()over(order by count(submit_time) desc) exam_cnt_rank_21
from exam_record join examination_info using(exam_id)
where year(submit_time)=2021 and month(submit_time)<=6
group by tag) 2021_table
using(tag)

order by growth_rate desc,exam_cnt_rank_21 desc