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

with data_f_use as (
    select tag, exam_id, year(submit_time) as complete_year
    from exam_record left join examination_info using (exam_id)
    where submit_time is not null and year(submit_time) in (2020, 2021)
    and month(submit_time) <= 6
), 
    data_f_cal as (
    select tag, complete_year, count(complete_year) exam_cnt
    from data_f_use
    group by tag, complete_year
), 
    data_ranked as (
    select tag, complete_year, exam_cnt,
        rank()over(partition by complete_year order by exam_cnt desc) exam_cnt_rank
    from data_f_cal 
)
    select tag, exam_cnt_20, exam_cnt_21, 
        concat(round( 100 * (exam_cnt_21 - exam_cnt_20) / exam_cnt_20 , 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, exam_cnt as exam_cnt_20,
            lead(exam_cnt, 1)over(partition by tag order by complete_year) as exam_cnt_21,
            exam_cnt_rank as exam_cnt_rank_20,
            lead(exam_cnt_rank, 1)over(partition by tag order by complete_year) as exam_cnt_rank_21
        from data_ranked
        ) a
    where exam_cnt_21 is not null
    order by growth_rate desc, exam_cnt_rank_21 desc