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

select
    t3.tag as tag,
    exam_cnt_20,
    exam_cnt_21,
    concat (
        ROUND((exam_cnt_21 / exam_cnt_20 -1) * 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,
            year (start_time) as start_year,
            count(score) as exam_cnt_21,
            rank() over (
                order by
                    count(score) desc
            ) as exam_cnt_rank_21
        from
            exam_record as t1
            left join examination_info as t2 on t1.exam_id = t2.exam_id
        where
            year (start_time) = 2021
            and month (start_time) <= 6
        group by
            tag,
            start_year
        having
            count(score) > 0
    ) as t4
    join (
        select
            tag,
            year (start_time) as start_year,
            count(score) as exam_cnt_20,
            rank() over (
                order by
                    count(score) desc
            ) as exam_cnt_rank_20
        from
            exam_record as t5
            left join examination_info as t6 on t5.exam_id = t6.exam_id
        where
            year (start_time) = 2020
            and month (start_time) <= 6
        group by
            tag,
            start_year
        having
            count(score) > 0
    ) as t3 on t3.tag = t4.tag
order by
    growth_rate desc,
    exam_cnt_rank_21 desc