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

运行时间超过96%

这道题逻辑很简单，分别用两个查询找出2021和2020的submit数量和排名，然后inner join起来就好了，再算一下growth_rate和delta_rank。

唯一要注意的就是算delta_rank的时候要把两个排名cast成unsigned格式。

with a as
(select tag, exam_cnt_20,
rank() over (order by exam_cnt_20 desc) exam_cnt_rank_20 from
(select 
tag,
count(submit_time) as exam_cnt_20
from examination_info inner join exam_record using(exam_id)
where date_format(submit_time,"%Y-%m-%d") >= "2020-01-01" and
      date_format(submit_time,"%Y-%m-%d") <= "2020-06-30"
group by tag) as temp),

b as
(select tag, exam_cnt_21,
rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21 from
(select 
tag,
count(submit_time) as exam_cnt_21
from examination_info inner join exam_record using(exam_id)
where date_format(submit_time,"%Y-%m-%d") >= "2021-01-01" and
      date_format(submit_time,"%Y-%m-%d") <= "2021-06-30"
group by tag) as temp)

select tag, exam_cnt_20, exam_cnt_21, 
concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,1), "%") as growth_rate,
exam_cnt_rank_20,
exam_cnt_rank_21,
cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta
from a inner join b using(tag)
order by growth_rate desc, exam_cnt_rank_21 desc