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

虽然优化很差，但浅显易懂😂

select t20.tag ,exam_cnt_20	, exam_cnt_21,
concat(round((exam_cnt_21 - exam_cnt_20)*100/exam_cnt_20,1),"%") growth_rate
,exam_cnt_rank_20
,exam_cnt_rank_21,
if(exam_cnt_rank_21 > exam_cnt_rank_20, exam_cnt_rank_21 - exam_cnt_rank_20, - (exam_cnt_rank_20 - exam_cnt_rank_21))
rank_delta
from
(
    select tag,count(submit_time) exam_cnt_20,
    rank() over(order by count(submit_time)desc) exam_cnt_rank_20
    from examination_info ei join exam_record er using(exam_id)
    where "2020"= year(submit_time) and date_format(submit_time,"%Y%m")<="202006"
    group by tag
)t20 join
(
    select tag,count(submit_time) exam_cnt_21,
    rank() over(order by count(submit_time)desc) exam_cnt_rank_21
    from examination_info ei join exam_record er using(exam_id)
    where "2021"= year(submit_time) and date_format(submit_time,"%Y%m")<="202106"
    group by tag
)t21 using(tag)
order by growth_rate desc, exam_cnt_rank_21 desc