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

with t3 as 
    (select 
        t2.tag,
        year(t1.start_time) as start_year,
        count(t1.submit_time) as exam_cnt,
        rank() over(partition by year(t1.start_time) order by count(t1.submit_time) desc) as exam_cnt_rank
    from
        exam_record t1
    left join
        examination_info t2
    on
        t1.exam_id = t2.exam_id
    where
        year(t1.start_time) in (2020,2021)
        and t1.submit_time is not null
        and month(t1.start_time) <= 6 
    group by
        t2.tag,start_year
    order by
        start_year,exam_cnt desc),
    t4 as (select tag from t3 group by t3.tag having count(t3.tag) > 1),
    t5 as (select tag,exam_cnt as exam_cnt_20 from t3 where start_year = 2020 and tag in (select tag from t4)),
    t6 as (select tag,exam_cnt as exam_cnt_21 from t3 where start_year = 2021 and tag in (select tag from t4)),
    t7 as (select t5.tag,concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,1),'%') as growth_rate from t5,t6 where t5.tag = t6.tag),
    t8 as (select tag,exam_cnt_rank as exam_cnt_rank_20
 from t3 where start_year = 2020 and tag in (select tag from t4)),
    t9 as (select tag,exam_cnt_rank as exam_cnt_rank_21
 from t3 where start_year = 2021 and tag in (select tag from t4)),
    t10 as (select t8.tag,cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta  from t8,t9 where t8.tag = t9.tag)

select
    t5.tag,
    t5.exam_cnt_20,
    t6.exam_cnt_21,
    t7.growth_rate,
    t8.exam_cnt_rank_20,
    t9.exam_cnt_rank_21,
    t10.rank_delta
from
    t5 left join t6 on t5.tag = t6.tag left join t7 on t5.tag = t7.tag left join t8 on t5.tag = t8.tag left join t9 on t5.tag = t9.tag left join t10 on t5.tag = t10.tag
order by
    t7.t7.growth_rate desc,t9.exam_cnt_rank_21 desc