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

#构建20年和21年的数据集 + 数据连接后进行计算
with t1 as (
    select
        ei.tag
        ,count(submit_time) as cnt
        ,rank() over(order by count(submit_time) desc) as rk
    from examination_info ei join exam_record er using(exam_id)
    where substr(er.submit_time,1,10) between'2020-01-01' and '2020-06-30'
    group by ei.tag
)
, t2 as (
    select
        ei.tag
        ,count(submit_time) as cnt
        ,rank() over(order by count(submit_time) desc) as rk
    from examination_info ei join exam_record er using(exam_id)
    where substr(er.submit_time,1,10) between'2021-01-01' and '2021-06-30'
    group by ei.tag
)
select 
    t2.tag
    ,t1.cnt
    ,t2.cnt
    ,concat(round((t2.cnt-t1.cnt)/t1.cnt*100,1),'%') as growth_rate
    ,t1.rk
    ,t2.rk
    ,(cast(t2.rk as signed)- cast(t1.rk as signed)) #这里需要注意下
from t2 join t1 using(tag)
order by growth_rate DESC
        ,t2.rk desc