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

SELECT tag, exam_cnt_20, exam_cnt_21,
    CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) / exam_cnt_20 * 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 (
    -- 2020年上半年各类试卷做完次数排名
    SELECT tag, exam_cnt_20,
        RANK()OVER(ORDER BY exam_cnt_20 DESC) AS exam_cnt_rank_20
    FROM (
        -- 计算2020年上半年各类试卷做完次数
        SELECT tag,
            COUNT(exam_id) AS exam_cnt_20
        FROM examination_info 
            JOIN exam_record
            USING(exam_id)
        WHERE submit_time IS NOT NULL
            AND YEAR(submit_time) = '2020'
            AND MONTH(submit_time) <= 6
        GROUP BY tag
    )exam_cnt_20_table
)t1 JOIN (
    -- 2021年上半年各类试卷做完次数排名
    SELECT tag, exam_cnt_21,
    RANK()OVER(ORDER BY exam_cnt_21 DESC) AS exam_cnt_rank_21
    FROM (
        -- 计算2021年上半年各类试卷做完次数
        SELECT tag,
            COUNT(exam_id) AS exam_cnt_21
        FROM examination_info 
            JOIN exam_record
            USING(exam_id)
        WHERE submit_time IS NOT NULL
            AND YEAR(submit_time) = '2021'
            AND MONTH(submit_time) <= 6
        GROUP BY tag
    )exam_cnt_21_table
)t2 USING(tag)
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC