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

with data_f_use as (
    select tag, year(start_time) as start_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, start_year, count(start_year) as complete_cnt
    from data_f_use
    group by tag, start_year
),
    data_2020 as (
    select tag, complete_cnt as exam_cnt_20,
        rank()over(order by complete_cnt desc) exam_cnt_rank_20
    from data_f_cal where start_year = 2020
),
    data_2021 as (
        select tag, complete_cnt as exam_cnt_21,
            rank()over(order by complete_cnt desc) exam_cnt_rank_21
        from data_f_cal where start_year = 2021 
)
  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_delt
  from data_2020 inner join data_2021 using (tag)
  order by growth_rate desc, exam_cnt_rank_21 desc