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

with a as 
(select tag, year(start_time) as start_year, count(submit_time) as exam_cnt, rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank
from examination_info as ei 
left join exam_record as er
on ei.exam_id=er.exam_id
where  month(start_time)<=6 and (year(start_time)=2021 or year(start_time)=2020)
group by year(start_time),tag
having count(submit_time) !=0)

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(format((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 a
group by tag
having count(start_year)=2
order by growth_rate desc,exam_cnt_rank_21 desc