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

# 2021年上半年各类试卷的做完次数  以及 同比
# 以及做完次数排名变化，按照增长率📈  和  21年排名降序输出
# 字段要求：tag\20年做完次数\21年做完次数\同比\20排名\21排名\排名增长数

# 表一：链接
with table1 as (
    select tag,uid,exam_id,submit_time
    from exam_record left join examination_info using(exam_id)
    where submit_time is not null 
    And year(submit_time) between 2020 and 2021
    AND month(submit_time) between 1 and 6
),
# 表二：按照tag、year进行分类计算
# 注意点：groupby按照tag、year分组，partition 按照year分组
# 注意点：排名允许并列且跳过，用rank
table2 as(
    select tag,year(submit_time) as start_year,COUNT(submit_time) as exam_cnt,rank()over(partition by year(submit_time) order by count(submit_time) desc) as exam_cnt_rank
    from table1
    group by tag,year(submit_time)
),
# 表三： 感觉要用lead函数？
table3 as(
    select tag,
    (case when start_year=2020 then exam_cnt else 0 end) as exam_cnt_20,
    lead(exam_cnt,1)over(partition by tag order by start_year) as exam_cnt_21,
    concat(round((lead(exam_cnt,1)over(partition by tag order by start_year)-(case when start_year=2020 then exam_cnt else 0 end))/(case when start_year=2020 then exam_cnt else 0 end)*100,1),"%") as growth_rate,	


    (case when start_year=2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20,
    lead(exam_cnt_rank,1)over(partition by tag order by start_year) as exam_cnt_rank_21,
    (lead(exam_cnt_rank,1)over(partition by tag order by start_year)-(case when start_year=2020 then exam_cnt_rank else 0 end)) as rank_delta
    from table2
)

select *
from table3
where growth_rate is not null
order by exam_cnt_20 asc