SQL141 试卷完成数同比2020年的

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

1、首先计算出上半年每个tag的作答记录以及排名
2、因为只能返回具有同比的tag记录，故可以用count()>=2筛选出tag，然后在聚合函数中使用case实现将行转为列，将20年的作答数和排名和21年的作答数和排名合并成一列
3、最后进行对应的计算即可

with t1 as(
select tag,year(start_time) start_year,count(exam_record.exam_id) exam_cnt
    ,rank() over(partition by year(start_time) order by year(start_time) asc,count(exam_record.exam_id) desc) exam_cnt_rank
from
    exam_record left join examination_info
    on examination_info.exam_id=exam_record.exam_id
where submit_time is not null and month(submit_time)<=6
group by tag,year(start_time)
),t2 as(
select tag,sum(case when start_year=2020 then exam_cnt end ) exam_cnt_20
    ,sum(case when start_year=2021 then exam_cnt end ) exam_cnt_21
    ,sum(case when start_year=2020 then exam_cnt_rank end ) exam_cnt_rank_20
    ,sum(case when start_year=2021 then exam_cnt_rank end ) exam_cnt_rank_21
from t1
where tag in (select tag from t1 group by tag having count(tag)>=2)
group by tag
)
select tag,exam_cnt_20,exam_cnt_21
    ,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') growth_rate
    ,exam_cnt_rank_20,exam_cnt_rank_21
    ,exam_cnt_rank_21-exam_cnt_rank_20 rank_delta
from t2
order by round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1) desc,exam_cnt_rank_21 desc