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

select
    a.tag,
    a.exam_cnt exam_cnt_20,
    b.exam_cnt exam_cnt_21,
    concat (
        round(((b.exam_cnt / a.exam_cnt) -1) * 100, 1),
        '%'
    ) growth_rate,
    a.exam_cnt_rank exam_cnt_rank_20,
    b.exam_cnt_rank exam_cnt_rank_21,
    cast(b.exam_cnt_rank as signed) - cast(a.exam_cnt_rank as signed) rank_delta
from
    (
        select
            tag,
            year (start_time) start_year,
            sum(
                case
                    when submit_time is not null then 1
                    else 0
                end
            ) exam_cnt,
            rank() over (
                order by
                    sum(
                        case
                            when submit_time is not null then 1
                            else 0
                        end
                    ) desc
            ) exam_cnt_rank
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
        where
            year (start_time) = 2020
            and month (start_time) <= 6
        group by
            tag,
            year (start_time)
        having
            exam_cnt != 0
    ) a
    join (
        select
            tag,
            year (start_time) start_year,
            sum(
                case
                    when submit_time is not null then 1
                    else 0
                end
            ) exam_cnt,
            rank() over (
                order by
                    sum(
                        case
                            when submit_time is not null then 1
                            else 0
                        end
                    ) desc
            ) exam_cnt_rank
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
        where
            year (start_time) = 2021
            and month (start_time) <= 6
        group by
            tag,
            year (start_time)
        having
            exam_cnt != 0
    ) b on a.tag = b.tag
order by
    growth_rate desc,
    exam_cnt_rank_21 desc