题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
select tag,exam_cnt_20,exam_cnt_21,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 (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, concat(round(((exam_cnt_21 - exam_cnt_20)/exam_cnt_20) * 100,1) , '%' ) as growth_rate from (select tag, sum(if(date_format(submit_time,'%Y%m') >= 202101 and date_format(submit_time,'%Y%m') <= 202106 ,1,0) ) as exam_cnt_21, sum(if(date_format(submit_time,'%Y%m') >= 202001 and date_format(submit_time,'%Y%m') <= 202006 ,1,0) ) as exam_cnt_20 from exam_record right join examination_info using(exam_id) group by tag )t1 )t2 where exam_cnt_21 <> 0 and exam_cnt_20 <> 0 order by growth_rate desc, exam_cnt_rank_21 desc