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

with a1 as 
# 先把会用到的连接起来，去掉下半年数据
    (select ei.exam_id,ei.tag,ei.difficulty,
        er.uid,er.start_time,er.submit_time,er.score
    from examination_info ei
     join exam_record er on ei.exam_id = er.exam_id
    where month(submit_time)<7),

a2 as 
# 根据提交年份计数
(select 
    tag,year(submit_time) start_year,
    count(exam_id) exam_cnt
from a1 
group by tag,start_year),

a3 as 
# 然后再做一次窗口函数
(
    select tag,start_year,exam_cnt,
        rank() over (partition by start_year order by exam_cnt desc ) exam_cnt_rank
    from a2
)

# 最后根据支付年份计算对应指标就行
select tag,
    sum(if(start_year=2020,exam_cnt,0)) exam_cnt_20,
    sum(if(start_year=2021,exam_cnt,0)) exam_cnt_21,
    concat(round(((sum(if(start_year=2021,exam_cnt,0))-sum(if(start_year=2020,exam_cnt,0)))/sum(if(start_year=2020,exam_cnt,0)))*100,1),'%') growth_rate,
    sum(if(start_year=2020,exam_cnt_rank,0)) exam_cnt_rank_20,
    sum(if(start_year=2021,exam_cnt_rank,0)) exam_cnt_rank_21,
    sum(if(start_year=2021,exam_cnt_rank,0))-sum(if(start_year=2020,exam_cnt_rank,0)) rank_delta
From a3
group by tag
having exam_cnt_20>0 and exam_cnt_21>0
order by growth_rate desc ,exam_cnt_rank_21 desc