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

SELECT tag,exam_cnt_20,exam_cnt_21,CONCAT(ROUND((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,dr2020 exam_cnt_rank_20,dr2021 exam_cnt_rank_21,dr2021-dr2020 rank_delta
FROM
(
SELECT tag,count(submit_time) exam_cnt_21,ROW_NUMBER() over(ORDER BY count(submit_time) desc) dr2021
FROM exam_record JOIN examination_info USING(exam_id)
WHERE DATE_FORMAT(submit_time,'%Y%m') BETWEEN '202101' and '202106'
GROUP BY tag)t1
JOIN
(
SELECT tag,count(submit_time) exam_cnt_20,ROW_NUMBER() over(ORDER BY count(submit_time) desc) dr2020
FROM exam_record JOIN examination_info USING(exam_id)
WHERE DATE_FORMAT(submit_time,'%Y%m') BETWEEN '202001' and '202006'
GROUP BY tag
)t2 USING(tag)
ORDER BY growth_rate,exam_cnt_21