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

#  各类试卷的做完次数  上半年  同期的增长率
# rank

with table1 as(
    select tag, date_format(start_time,"%Y") as start_year,
    date_format(submit_time,"%Y%m") as submit_time
    from exam_record left join examination_info using(exam_id)
    where month(submit_time) <= 6 
),
table2 as (
    select tag,start_year,
    count(submit_time) as exam_cnt
    # rank()over(partition by start_year order by submit_month) as exam_cnt_rank 
    from table1 
    group by tag, start_year
),
table3 as(
    select * , rank()over(partition by start_year order by exam_cnt desc) as exam_cnt_rank
    from table2 
),
table4 as(
    select tag , 
    max(if(start_year = '2020',exam_cnt,0)) as exam_cnt_20,
    max(if(start_year = '2021',exam_cnt,0)) as exam_cnt_21,
    max(if(start_year = '2020',exam_cnt_rank,0)) as exam_cnt_rank_20,
    max(if(start_year = '2021',exam_cnt_rank,0)) as exam_cnt_rank_21
    from table3
    group by tag
)
select tag,exam_cnt_20,exam_cnt_21,
concat(round(((exam_cnt_21 - exam_cnt_20)/exam_cnt_20) * 100,1),'%') as growth_rate,
exam_cnt_rank_20,exam_cnt_rank_21,
(exam_cnt_rank_21 - exam_cnt_rank_20) as rank_delta
from table4
where exam_cnt_rank_21 != 0 and exam_cnt_rank_20 != 0
order by growth_rate desc,exam_cnt_rank_21 desc