极简题解 | 自连接，逻辑清晰，代码简单
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
with a as (select #统计次数，窗口函数完成排名 exam_id ,year(submit_time) as start_year ,count(submit_time) as exam_cnt ,rank()over(partition by year(submit_time) order by count(submit_time) desc) as rk from exam_record where month(submit_time) <7 group by exam_id,start_year) select t1.tag, t2.exam_cnt as exam_cnt_20, t.exam_cnt as exam_cnt_21, concat(round(((t.exam_cnt-t2.exam_cnt)/t2.exam_cnt)*100,1),'%') as growth_rate ,t2.rk as exam_cnt_rank_20 ,t.rk as exam_cnt_rank_21 ,t.rk-ifnull(t2.rk,0) as rank_delta from a as t join a as t2 on t2.exam_id=t.exam_id #使用内连接，能连接上的就能计算增长率 join examination_info as t1 on t1.exam_id=t2.exam_id and t.start_year=2021 and t2.start_year=2020 #使用自连接分别取2020和2021 order by growth_rate desc,exam_cnt_rank_21 desc
