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

select t1.tag,t1.exam_cnt as exam_cnt_20,t2.exam_cnt as exam_cnt_21, CONCAT(ROUND((t2.exam_cnt/t1.exam_cnt-1)*100,1),'%') as growth_rate,t1.ranking as exam_cnt_rank_20,
t2.ranking as exam_cnt_rank_21,cast(t2.ranking as signed)-cast(t1.ranking as signed) as rank_delta
from
        (select *,rank()over(order by exam_cnt desc) as ranking
        from
        (select tag,count(submit_time) as exam_cnt
        from examination_info ei inner join exam_record er
        on ei.exam_id=er.exam_id
        where year(start_time)=2020 and month(start_time)<=6
        group by tag
        having count(submit_time)>0)t1)t1
inner join
        (select *,rank()over(order by exam_cnt desc) as ranking
        from
        (select tag,count(submit_time) as exam_cnt
        from examination_info ei inner join exam_record er
        on ei.exam_id=er.exam_id
        where year(start_time)=2021 and month(start_time)<=6
        group by tag
        having count(submit_time)>0)t1)t2
on t1.tag=t2.tag
order by growth_rate desc,exam_cnt_rank_21 desc