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

分别筛选出2021年上半年和2020年上半年每个tag被完成的次数和完成次数排名，用 WITH...AS 建立新表exam_cnt_21_firstHalf和exam_cnt_20_firstHalf，然后用 INNER JOIN 连接两个表，计算增长率和排名变化

WITH exam_cnt_21_firstHalf AS (
    SELECT tag,
        exam_cnt_21,
        RANK()OVER(ORDER BY exam_cnt_21 DESC) AS exam_cnt_rank_21
    FROM (
        SELECT tag, COUNT(submit_time) AS exam_cnt_21
        FROM exam_record JOIN examination_info
            USING(exam_id)
        WHERE submit_time IS NOT NULL
            AND YEAR(submit_time) = '2021'
            AND MONTH(submit_time) <= 6
        GROUP BY tag
    ) table1
),
exam_cnt_20_firstHalf AS (
    SELECT tag,
        exam_cnt_20,
        RANK()OVER(ORDER BY exam_cnt_20 DESC) AS exam_cnt_rank_20
    FROM (
        SELECT tag, COUNT(submit_time) AS exam_cnt_20
        FROM exam_record JOIN examination_info
            USING(exam_id)
        WHERE submit_time IS NOT NULL
            AND YEAR(submit_time) = '2020'
            AND MONTH(submit_time) <= 6
        GROUP BY tag
    ) table2
)

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 exam_cnt_21_firstHalf JOIN exam_cnt_20_firstHalf
    USING(tag)
WHERE exam_cnt_20 IS NOT NULL
    AND exam_cnt_21 IS NOT NULL
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC