难度没想象中那么大

-- 第一步：过渡表，做出2020年和2021年不同tag的做完次数和做完次数排名
with t AS
(
    select *,RANK()over(order by exam_cnt desc) as exam_cnt_rank
    from(select tag,year(submit_time) as start_year,count(score) as exam_cnt
    from exam_record t
    join examination_info t1 on t.exam_id = t1.exam_id
    where date_format(submit_time,"%Y%m")<='202006' and date_format(submit_time,"%Y%m")>='202001'
    group by tag,start_year) t
    union all
    select *,RANK()over(order by exam_cnt desc) as exam_cnt_rank
    from(select tag,year(submit_time) as start_year,count(score) as exam_cnt
    from exam_record t
    join examination_info t1 on t.exam_id = t1.exam_id
    where date_format(submit_time,"%Y%m")<='202106' and date_format(submit_time,"%Y%m")>='202101'
    group by tag,start_year) t1
)
-- 第二步：一个个列出来就完事儿了
select tag,
       sum(if(start_year=2020,exam_cnt,0)) as exam_cnt_20,
       sum(if(start_year=2021,exam_cnt,0)) as exam_cnt_21,
       CONCAT(round((sum(if(start_year=2021,exam_cnt,0))-sum(if(start_year=2020,exam_cnt,0)))/sum(if(start_year=2020,exam_cnt,0))*100,1),"%") as growth_rate,
       sum(if(start_year=2020,exam_cnt_rank,0)) as exam_cnt_rank_20,
       sum(if(start_year=2021,exam_cnt_rank,0)) as exam_cnt_rank_21,
       sum(if(start_year=2021,exam_cnt_rank,0))-sum(if(start_year=2020,exam_cnt_rank,0)) as rank_delta
from t
where tag in(select tag from t group by tag having count(1) =2)
group by tag
order by growth_rate desc,rank_delta DESC