1.2021年上半年做完次数，排名 2.2020年上半年做完次数，排名 3.对比增长率，排名变化

with t1 as( select exam_id,count()cnt1, rank() over(order by count() desc) rank1 from exam_record where score is not null and year(start_time)=2021 and month(start_time)<=6 group by exam_id), t2 as( select exam_id,count()cnt2, rank() over(order by count() desc) rank2 from exam_record where score is not null and year(start_time)=2020 and month(start_time)<=6 group by exam_id)

select tag,
cnt2 exam_cnt_20, cnt1 exam_cnt_21, concat(round((cnt1-cnt2)/cnt2*100,1),'%') as growth_rate, rank2 exam_cnt_rank_20, rank1 exam_cnt_rank_21, cast(rank1 as signed)-cast(rank2 as signed) rank_delta from examination_info a inner join t1 on a.exam_id=t1.exam_id inner join t2 on a.exam_id=t2.exam_id order by rank_delta desc,rank1 desc