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

select tag,cnt_20,cnt_21,growth,ra_20,ra_21,cast(ra_21 as signed) - cast(ra_20 as signed) as ra_delta 
from(
select tag,cnt_20,cnt_21,
concat(round((cnt_21-cnt_20)/cnt_20*100,1),'%') as growth,
rank() over(order by cnt_20 desc) as ra_20,
rank() over(order by cnt_21 desc) as ra_21
from(
select b.tag,
count(if(date(a.submit_time) between '2020-01-01' and '2020-06-30' ,a.score,null)) as cnt_20,
count(if(date(a.submit_time) between '2021-01-01' and '2021-06-30' ,a.score,null)) as cnt_21
from exam_record a,examination_info b
where a.exam_id=b.exam_id group by b.tag)c
order by 4 desc,6 desc)d
where cnt_20>0 and cnt_21>0