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

select tag,exam_cnt_20,exam_cnt_21,concat(round(100*((exam_cnt_21-exam_cnt_20)/exam_cnt_20),1),'%') growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) rank_delta from 
(
select *,rank()over( order by exam_cnt_20 desc) exam_cnt_rank_20,rank()over(order by exam_cnt_21 desc) exam_cnt_rank_21 from
(
select tag,count(if(year(start_time)='2020',1,null)) exam_cnt_20,count(if(year(start_time)='2021',1,null)) exam_cnt_21 from
(
select tag,start_time from examination_info a left join exam_record b
on a.exam_id = b.exam_id
where month(b.start_time) between 1 and 6
and submit_time is not null
) c
group by tag
) d 
) e
where exam_cnt_21 !=0 and exam_cnt_20 != 0
order by growth_rate desc,exam_cnt_rank_21 desc;