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

Select tag, 
exam_cnt_20, exam_cnt_21, 
concat(round((exam_cnt_21 - exam_cnt_20)/exam_cnt_20 * 100,1),'%' ) as 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 tag,
        sum(case when start_year = '2020' then  exam_cnt end ) as exam_cnt_20,
        sum(case when start_year = '2021' then exam_cnt end ) as exam_cnt_21,
        sum(case when start_year = '2020' then exam_cnt_rank end ) as exam_cnt_rank_20,
        sum(case when start_year = '2021' then exam_cnt_rank end ) as exam_cnt_rank_21
    from 
    (Select tag, date_format(start_time_origin, '%Y' ) as start_year, count(score) as exam_cnt, rank() over(partition by date_format(start_time_origin, '%Y') order by count(score) desc ) as exam_cnt_rank

    from (
        Select tag, start_time as start_time_origin, date_format(start_time, '%Y%m' ) as start_month, score 
        from exam_record er left join examination_info ei on er.exam_id = ei.exam_id 
        where score is not null and ((date_format(start_time, '%Y%m' ) between '202001' and '202006') or (date_format(start_time, '%Y%m' ) between '202101' and '202106'))) t1

    group by tag, date_format(start_time_origin, '%Y') ) t2
    group by tag) t3

where exam_cnt_20 is not null and exam_cnt_21 is not null
order by growth_rate desc, exam_cnt_rank_21 desc