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

# 1. 2021年、2020年上半年各类试卷的做完次数, 以及排名
with newtable as (
    select 
        tag, 
        year(start_time) start_year,
        count(score) exam_cnt,
        rank() over (partition by year(start_time) order by count(score) DESC) exam_cnt_rank
    from exam_record 
    join examination_info using(exam_id)
    where year(start_time) in (2020,2021) and month(start_time)<=6
    group by tag, year(start_time)
    having exam_cnt > 0     #过滤作答数为0的记录
)
# 2. 合并，求增长率、排名变化
select 
    a.tag tag, 
    a.exam_cnt exam_cnt_20,
    b.exam_cnt exam_cnt_21,
    concat(round((b.exam_cnt - a.exam_cnt)/a.exam_cnt*100, 1) ,"%")  growth_rate,
    a.exam_cnt_rank exam_cnt_rank_20,
    b.exam_cnt_rank exam_cnt_rank_21,
    CAST(b.exam_cnt_rank as SIGNED) - CAST(a.exam_cnt_rank as SIGNED) rank_delta  #直接相减出现报错BIGINT UNSIGNED，需要转化格式为整数signed
from newtable a join newtable b 
on a.tag=b.tag and a.start_year=2020 and b.start_year=2021 
order by growth_rate DESC, exam_cnt_rank_21 DESC