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

# 2020年上半年的作答次数与排名信息作为左表，计算年度同比时需要有2020年2021年的数据指标
select c.tag
, c.exam_2020cnt
, d.exam_2021cnt
, concat(round(((d.exam_2021cnt/c.exam_2020cnt)-1)*100,1),'%') as growth_rate
, c.exam_2020_rank
, d.exam_2021_rank 
, (cast(d.exam_2021_rank as SIGNED) - cast(c.exam_2020_rank as SIGNED)) as rank_delta
from(
    select i.tag,a.start_year,a.exam_2020cnt,rank()over(order by a.exam_2020cnt desc) as exam_2020_rank
    from(
        select exam_id,date_format(start_time,'%Y') as start_year, count(score) as exam_2020cnt
        from exam_record e
        where year(start_time) = 2020
        and  date_format(start_time,'%Y%m') <= '202006'
        group by 1,2
        )a
    left join examination_info i on i.exam_id = a.exam_id
    group by 1,2
    ) c
left join
    (
    select i.tag,b.start_year,b.exam_2021cnt,rank()over(order by b.exam_2021cnt desc) as exam_2021_rank
    from(
        select exam_id,date_format(start_time,'%Y') as start_year, count(score) as exam_2021cnt
        from exam_record e
        where year(start_time) = 2021
        and  date_format(start_time,'%Y%m') <= '202106'
        and score is not NULL
        group by 1,2
        )b  
    left join examination_info i on i.exam_id = b.exam_id
    group by 1,2
 )d
    on c.tag = d.tag
    where d.exam_2021cnt is not NULL
    order by 4 desc,6 desc