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

SELECT  tag, 
        exam_cnt_20, 
        exam_cnt_21,  
        CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) / exam_cnt_20 * 100, 1), '%') growth_rate,
        rank_20,
        rank_21,
        CAST(rank_21 as SIGNED) - CAST(rank_20 AS SIGNED) rank_delta
FROM(
    SELECT tag,   -- 第二个子查询把排名补充上后再套一层就可以输出结果了，简简单单
           exam_cnt_20, 
           exam_cnt_21, 
           RANK()OVER(ORDER BY exam_cnt_20 DESC) rank_20, 
           RANK()OVER(ORDER BY exam_cnt_21 DESC) rank_21
    FROM(    -- 第一个子查询先把格式整理好和数据筛选好，以及把题目所需的完成次数算出来，tag做分类
        SELECT tag,                                             
               SUM(IF(YEAR(submit_time) = 2020, 1, 0)) exam_cnt_20,         
               SUM(IF(YEAR(submit_time) = 2021, 1, 0)) exam_cnt_21
        FROM exam_record LEFT JOIN examination_info USING(exam_id)
        WHERE MONTH(start_time) <= 6
        GROUP BY tag
    )a
)b
WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0
ORDER BY growth_rate DESC, rank_21 DESC