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

比较笨的办法

with
t1 as(
    select
        tag,
        submit_time
    from
        examination_info as ei
        inner join
        exam_record as er using(exam_id)
    where
        submit_time between '2020-01-01' AND '2020-06-30 23:59:59'
        or
        submit_time between '2021-01-01' AND '2021-06-30 23:59:59'
),
t2 as(
    select
        tag,
        date_format(submit_time,'%Y') as start_year,
        count(submit_time) as exam_cnt,
        cast(rank() over (partition by date_format(submit_time,'%Y') order by count(submit_time) desc) as signed) as exam_cnt_rank
    from
        t1
    group by
        tag,
        start_year
),
t3 as(
    select
        tag,
        exam_cnt as exam_cnt_20,
        exam_cnt_rank as exam_cnt_rank_20
    from
        t2
    where
        start_year=2020
),
t4 as(
    select
        tag,
        exam_cnt as exam_cnt_21,
        exam_cnt_rank as exam_cnt_rank_21
    from
        t2
    where
        start_year=2021
)

select
    t4.tag,
    t3.exam_cnt_20,
    t4.exam_cnt_21,
    concat(round((t4.exam_cnt_21 - t3.exam_cnt_20) / t3.exam_cnt_20 * 100, 1),'%') as growth_rate,
    t3.exam_cnt_rank_20,
    t4.exam_cnt_rank_21,
    t4.exam_cnt_rank_21 - t3.exam_cnt_rank_20 as rank_delta
from
    t4 inner join t3 on t4.tag = t3.tag
order by
    growth_rate desc,
    t4.exam_cnt_rank_21 desc;