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

题目描述
请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b

思路：
1.先计算2020,2021年上半年每一类试卷

select e.tag,year(start_time) as start_year,count(score) exam_cnt
from examination_info as e join exam_record as r on e.exam_id=r.exam_id
where month(start_time)<=6 and year(start_time)=2020
group by e.tag,start_year
having exam_cnt!=0

select e.tag,year(start_time) as start_year,count(score) exam_cnt
from examination_info as e join exam_record as r on e.exam_id=r.exam_id
where month(start_time)<=6 and year(start_time)=2021
group by e.tag,start_year
having exam_cnt!=0

---

2. 计算排名

select *,rank() over (order by exam_cnt desc) as exam_cnt_rank_2021
from (
    select e.tag,year(start_time) as start_year,count(score) exam_cnt_2021
    from examination_info as e join exam_record as r on e.exam_id=r.exam_id
    where month(start_time)<=6 and year(start_time)=2021
    group by e.tag,start_year
    having exam_cnt!=0
)
select *,rank() over (order by exam_cnt desc) as exam_cnt_rank_2020
from (
    select e.tag,year(start_time) as start_year,count(score) exam_cnt_2020
    from examination_info as e join exam_record as r on e.exam_id=r.exam_id
    where month(start_time)<=6 and year(start_time)=2020
    group by e.tag,start_year
    having exam_cnt!=0
)

---

select t2021.tag,
t2020.exam_cnt_20,
t2021.exam_cnt_21,
concat(round((t2021.exam_cnt_21-t2020.exam_cnt_20)/t2020.exam_cnt_20*100,1),"%") as growth_rate,
t2020.exam_cnt_rank_20,
t2021.exam_cnt_rank_21,
(cast(t2021.exam_cnt_rank_21 as signed)-cast(t2020.exam_cnt_rank_20 as signed) )as rank_delta
from (
    select *,rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21
    from (
        select e.tag,year(start_time) as start_year,count(score) exam_cnt_21
        from examination_info as e join exam_record as r on e.exam_id=r.exam_id
        where month(start_time)<=6 and year(start_time)=2021
        group by e.tag,start_year
        having exam_cnt_21!=0
    ) as t21
) as t2021 ,(
    select *,rank() over (order by exam_cnt_20 desc) as exam_cnt_rank_20
    from (
        select e.tag,year(start_time) as start_year,count(score) exam_cnt_20
        from examination_info as e join exam_record as r on e.exam_id=r.exam_id
        where month(start_time)<=6 and year(start_time)=2020
        group by e.tag,start_year
        having exam_cnt_20!=0
    ) as t20
)as t2020 
where t2021.tag=t2020.tag 
order by growth_rate desc,exam_cnt_rank_21 desc