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

with cnt_2021_2020 AS
(
select tag,start_year,exam_cnt,rank()over(partition by start_year order by exam_cnt desc) as exam_cnt_rank
from 
 (select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt
 from exam_record er left join examination_info ef on er.exam_id=ef.exam_id
 where year(start_time)='2021' and month(start_time)<=6
 union all 
 select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt
 from exam_record er left join examination_info ef on er.exam_id=ef.exam_id
 where year(start_time)='2020' and month(start_time)<=6) t2
)


select t3.tag
,t3.exam_cnt as exam_cnt_20
,t4.exam_cnt as exam_cnt_21
,concat(left((t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt*100,5),'%') as growth_rate
,t3.exam_cnt_rank as exam_cnt_rank_20
,t4.exam_cnt_rank as exam_cnt_rank_21
,if(t4.exam_cnt_rank>t3.exam_cnt_rank,t4.exam_cnt_rank-t3.exam_cnt_rank,-(t3.exam_cnt_rank-t4.exam_cnt_rank)) as rank_delta
from (select * from cnt_2021_2020 where start_year='2020') t3
left join 
(select * from cnt_2021_2020 where start_year='2021') t4 on t3.tag=t4.tag
where t4.exam_cnt>=1
order by growth_rate desc,exam_cnt_rank_21 desc

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
select * ,exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta
from(
 select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate,
 rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20,
 rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21
 from(
select i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20,
count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21
from exam_record r left join examination_info i on r.exam_id=i.exam_id
 group by i.tag
 ) a  )b
 where exam_cnt_20*exam_cnt_21>0 
 order by growth_rate desc,exam_cnt_rank_21 desc
 ;

select *,cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
from(
 select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate,
 rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20,
 rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21
 from(
select i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20,
count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21
from exam_record r left join examination_info i on r.exam_id=i.exam_id
 group by i.tag
 ) a  )b
 where exam_cnt_20*exam_cnt_21>0
 order by growth_rate desc,exam_cnt_rank_21 desc
 ;

【场景】：百分比格式

【分类】：专用窗口函数、select条件

分析思路

难点：

长整型的数据类型要求不能有负号产生，用cast函数转换数据类型为signed。

（1）统计2020年、2021年上半年各类试卷的做完次数和做完次数排名

• [条件]：if(date(start_time) between '20200101' and '20200630',start_time,null) 和 if(date(start_time) between '20210101' and '20210630',start_time,null)
• [使用]：rank() over (order by 做完次数 desc)

（2）计算同期的增长率以及做完次数排名变化，按增长率和21年排名降序输出

• [条件]：exam_cnt_21*exam_cnt_20 <> 0
• [使用]：order by growth_rate desc, exam_cnt_rank_21 desc

注意：
1.增长率计算公式：(exam_cnt_21-exam_cnt_20)/exam_cnt_20
2.做完次数排名变化（2021年和2020年比排名升了或者降了多少）
3.计算公式：exam_cnt_rank_21-exam_cnt_rank_20
4.条件：exam_cnt_20、exam_cnt_21都不为空
5.长整型的数据类型要求不能有负号产生，所以用cast函数转换数据类型为signed。如果不用cast函数两数相减时一旦产生负数会报错：BIGINT UNSIGNED（长整型数字无符号）。所以要转换数据类型为signed类型，保证产生负数也不会报错。对比增长率，明明相减的时候它也会产生负数，为什么就不用cast处理呢？因为round函数对正数、负数都可以做处理。

最终结果

select 查询结果 [试卷类型;2020年做完次数;2021年做完次数;增长率;2020年做完次数排名;2021年做完次数排名;做完次数排名变化]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [2020年和2021年做完次数不为空]
group by 分组条件 [用户ID;月份]
order by 对查询结果排序 [增长率、21年排名降序];

题解里面隐藏了4种判断 2020年上半年的 方法哦

求解代码

方法一

with子句 + 一步步拆解

with 
    main as(
        #2020年上半年各类试卷的做完次数
        select
            tag,
            '2020' as start_year_20,
            count(start_time) as exam_cnt_20
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and 7 > month(start_time)
        and year(start_time) = 2020
        and submit_time is not null
        group by tag
    ),
    attr as(
        #2021年上半年各类试卷的做完次数
        select
            tag,
            '2021' as start_year_21,
            count(start_time) as exam_cnt_21
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and 7 > month(start_time)
        and year(start_time) = 2021
        and submit_time is not null
        group by tag
    ),
    main1 as(
        #2020上半年做完次数排名
        select
            tag,
            start_year_20,
            exam_cnt_20,
            rank() over (order by exam_cnt_20 desc) as exam_cnt_rank_20
        from main
    ),
    attr1 as(
        #2021上半年做完次数排名
        select
            tag,
            start_year_21,
            exam_cnt_21,
            rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21
        from attr
    )

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,1),'%') as growth_rate,
    exam_cnt_rank_20,
    exam_cnt_rank_21,
    cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
from main1
join attr1 using(tag)
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

代码优化

将条件 (2020年、2021年上半年) 放在 select 语句中

with 
    main as(
        #2020年、2021年上半年各类试卷的做完次数和做完次数排名
        select
            tag,
            count(if(date(start_time) between '20200101' and '20200630',start_time,null)) as exam_cnt_20,
            count(if(date(start_time) between '20210101' and '20210630',start_time,null)) as exam_cnt_21,
            rank() over (order by count(if(date(start_time) between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20,
            rank() over (order by count(if(date(start_time) between '20210101' and '20210630',start_time,null)) desc) as exam_cnt_rank_21
        from examination_info
        join exam_record using(exam_id)
        where submit_time is not null
        group by tag
    )

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,1),'%') as growth_rate,
    exam_cnt_rank_20,
    exam_cnt_rank_21,
    cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
from main
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

main 表输出结果：

1    算法|0|3|4|1    1
2    SQL|3|2|1|2        
3    C++|3|0|1|3        
4    PYTHON|2|0|3|3

方法二

from 子查询

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,1),'%') as growth_rate,
    exam_cnt_rank_20,
    exam_cnt_rank_21,
    cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
from(
    #2020年、2021年上半年各类试卷的做完次数和做完次数排名
    select
        tag,
        count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) as exam_cnt_20,
        count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) as exam_cnt_21,
        rank() over (order by count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20,
        rank() over (order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) as exam_cnt_rank_21
    from examination_info
    join exam_record using(exam_id)
    where submit_time is not null
    group by tag
) main
where exam_cnt_21*exam_cnt_20 <> 0
order by growth_rate desc, exam_cnt_rank_21 desc

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(
    select tag,
        sum(if(start_year = 2020, exam_cnt, 0)) as exam_cnt_20,
        sum(if(start_year = 2021, exam_cnt, 0)) as exam_cnt_21,
        sum(if(start_year = 2020, exam_cnt_rank, 0)) as exam_cnt_rank_20,
        sum(if(start_year = 2021, exam_cnt_rank, 0)) as exam_cnt_rank_21
    from(
        SELECT tag, 
            year(start_time) as start_year,
            count(submit_time) as exam_cnt,
            rank() over(order by count(submit_time) desc) as exam_cnt_rank
        from exam_record er right join examination_info ei
        on er.exam_id = ei.exam_id
        where year(start_time) = 2020 and month(start_time) <= 6
        group by tag

        union ALL

        SELECT tag, 
            year(start_time) as start_year,
            count(submit_time) as exam_cnt,
            rank() over(order by count(submit_time) desc) as exam_cnt_rank
        from exam_record er right join examination_info ei
        on er.exam_id = ei.exam_id
        where year(start_time) = 2021 and month(start_time) <= 6
        group by tag
    ) as table1
    group by tag
) as table2
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc, exam_cnt_rank_21 desc

#1.先分步骤走，思路会非常轻松，主要思路写完，
#2.然后逐渐完善细节，自测通过，
#3.提交后继续扣细节最后完成
#当然如果细节能提前想到会更好，如果不能也没事

# 2021年上半年各类试卷的做完次数相比2020年上半年
# 同期的增长率（百分比格式，保留1位小数）
with temp1 as(
select exam_id,
    count(score) exam_cnt_20,
    rank() over(order by count(score) desc) exam_cnt_rank_20
from exam_record
where year(submit_time)=2020 and month(submit_time)<7
group by exam_id
),
    temp2 as(
select exam_id,
    count(score) exam_cnt_21,
    rank() over(order by count(score) desc) exam_cnt_rank_21
from exam_record
where year(submit_time)=2021 and month(submit_time)<7
group by exam_id
)

# 以及做完次数排名变化，
select tag,
        exam_cnt_20,
        exam_cnt_21,
        concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),"%") growth_rate,
        exam_cnt_rank_20,
        exam_cnt_rank_21,
        cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed)  rank_delta
from temp1 join temp2 on temp1.exam_id=temp2.exam_id
            join examination_info e on temp1.exam_id=e.exam_id
order by growth_rate desc,exam_cnt_rank_21 desc
# 按增长率和21年排名降序输出。

select tag
,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20
,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_cnt_21
,concat(round((sum(case when start_year =2021 then exam_cnt else 0 end)-sum(case when start_year =2020 then exam_cnt else 0 end))
/sum(case when start_year =2020 then exam_cnt else 0 end)*100,1),'%') as growth_rate
,sum(case when start_year =2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) as exam_cnt_rank_21
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) - sum(case when start_year =2020 then exam_cnt_rank else 0 end) as rank_delta 
from (-- 关键是这里求出完成次数级排名 要使用rank美式排名
    select tag,year(start_time) as start_year,
    count(submit_time) as exam_cnt,
    rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank
    from exam_record t join examination_info t1 using(exam_id)
    where month(start_time)<=6
    group by tag,year(start_time)
) t
group by tag
having exam_cnt_20 >0 and exam_cnt_21>0
order by growth_rate desc,exam_cnt_rank_21 desc

先求出完成次数级排名 要使用rank美式排名

然后在这个基础之上，进行大量的case when的操作，新建列。

优点麻烦，自测的时候没错误，提交的时候rank_delta时候老是报错，参考了评论里面的大神用的cast，解决了
select a.tag,a.cnt1 exam_cnt_20,b.cnt2 exam_cnt_20,concat(round((b.cnt2 - a.cnt1)*100/a.cnt1,1),'%') growth_rate,
a.t_rank1 exam_cnt_rank_20,b.t_rank2 exam_cnt_rank_21,cast(b.t_rank2 as signed) - cast(a.t_rank1 as signed) as rank_delta from
(select ei.tag,count(er.submit_time) cnt1,
rank() over (order by count(er.submit_time) desc) t_rank1 from exam_record er 
inner join examination_info ei on ei.exam_id = er.exam_id
where date_format(er.submit_time,'%Y%m') between '202001' and '202006'
group by ei.tag) as a inner join 
(select ei.tag,count(er.submit_time) cnt2,
rank() over (order by count(er.submit_time) desc) t_rank2
from exam_record er inner join examination_info ei on ei.exam_id = er.exam_id
where date_format(er.submit_time,'%Y%m') between '202101' and '202106'
group by ei.tag) as b on a.tag = b.tag
group by a.tag
order by growth_rate desc,exam_cnt_rank_21 desc

with e_e as (
    select er.exam_id, tag, score, start_time, submit_time
    from exam_record as  er left join examination_info as ei
    on er.exam_id = ei.exam_id
)
select t21.tag
, exam_cnt_20
, exam_cnt_21
, concat(round(100*(exam_cnt_21 - exam_cnt_20)/exam_cnt_20,1),'%') as growth_rate
, exam_cnt_rank_20
, exam_cnt_rank_21
, (cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed)) as  rank_delta
from 
(
select tag
, count(score) as exam_cnt_20
, rank() over(order by count(score) desc) exam_cnt_rank_20
from e_e
where date_format(submit_time,'%Y%m') between '202001' and '202006'
and submit_time is not null
group by tag) as t20 inner join 
(
select tag
, count(score) as exam_cnt_21
, rank() over(order by count(score) desc) exam_cnt_rank_21
from e_e
where date_format(submit_time,'%Y%m') between '202101' and '202106'
and submit_time is not null
group by tag) as t21  on t20.tag = t21.tag
order by growth_rate desc, exam_cnt_21 asc;

SELECT *,
cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta
FROM
(SELECT a.tag,
COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) AS exam_cnt_20,
COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) AS exam_cnt_21,
CONCAT(ROUND((COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL))-COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)))/COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL))*100,1),'%') AS growth_rate,
RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_20,
RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_21
FROM examination_info a INNER JOIN exam_record b 
ON a.exam_id=b.exam_id
GROUP BY a.tag) t1
WHERE exam_cnt_20*exam_cnt_21>0
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC

select tag,exam_cnt_20,exam_cnt_21, concat(round((exam_cnt_21 - exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,CAST(exam_cnt_rank_21 AS SIGNED ) - CAST(exam_cnt_rank_20 AS SIGNED )  AS rank_delta
from
   (
    select *
    from 
        (select tag,year(start_time) as 2020_years,count(submit_time) as exam_cnt_20,rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank_20
        from exam_record left join examination_info using(exam_id)
        where year(start_time) = 2020 and month(start_time) <=6 
        group by tag,year(start_time)
        having count(submit_time) != 0 ) a #将2020年上半年需要的数据提取出来
    join
        (select tag,year(start_time) as 2021_years,count(submit_time) as exam_cnt_21,rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank_21
         from exam_record left join examination_info using(exam_id)
         where year(start_time) = 2021 and month(start_time) <=6 
         group by tag,year(start_time) 
         having count(submit_time) != 0 ) b #将2021年上半年需要的数据提取出来
    using(tag)
    ) c #内连接，将两个时期都有的记录提取出来
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC  #按要求计算数值并排序

#

select tag, exam_cnt_20, exam_cnt_21, 
 concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
 exam_cnt_rank20, exam_cnt_rank21,
(cast(exam_cnt_rank21 as signed)-cast( exam_cnt_rank20 as signed)) rank_delta
from
(select tag,
sum(if( start_year=2020 , exam_cnt ,0)) exam_cnt_20,
sum(if(start_year=2021,exam_cnt,0)) exam_cnt_21,
sum(if(start_year=2020,exam_cnt_rank,0)) exam_cnt_rank20,
sum(if(start_year=2021,exam_cnt_rank,0)) exam_cnt_rank21
from

(select tag,year(submit_time) start_year ,count(*) exam_cnt ,
rank() over(partition by year(submit_time) order by count(*)  desc) exam_cnt_rank
from  exam_record e_r join examination_info e_i
using(exam_id) 
where submit_time is not null 
 and year(submit_time) BETWEEN 2020 and 2021 
 and month(submit_time)<=6
group by tag,year(submit_time))mm

group by tag
)nn
where exam_cnt_21 <>0
and exam_cnt_20 <>0
order by growth_rate desc, exam_cnt_rank21 desc

with t1 as (
    select tag,
    count(if(year(submit_time) = 2020 and month(submit_time) <= 6,1,null)) exam_cnt_20,
    count(if(year(submit_time) = 2021 and month(submit_time) <= 6,1,null)) exam_cnt_21
    from exam_record
    left join examination_info using(exam_id)
    group by tag
),

t2 as (
    select tag,exam_cnt_20,exam_cnt_21,
    rank() over(order by exam_cnt_20 desc) exam_cnt_rank_20,
    rank() over(order by exam_cnt_21 desc) exam_cnt_rank_21
    from t1
)

select tag,exam_cnt_20,exam_cnt_21, growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,rank_delta
from (
    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,
    cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta
    from t2
) t3
where exam_cnt_20 > 0 and exam_cnt_21 > 0
order by growth_rate desc, exam_cnt_rank_21 desc

select tag,e20 exam_cnt_20,e21 exam_cnt_21
,concat(round((e21/e20-1)*100,1),'%') growth_rate
,rk20 exam_cnt_rank_20,rk21 exam_cnt_rank_21
,cast(rk21 as signed)-cast(rk20 as signed) rank_delta 
from(select *
    ,rank()over(order by e20 desc) rk20
    ,rank()over(order by e21 desc) rk21
    from(select tag
        ,count(if(month(submit_time)<7 and year(submit_time)=2020,1,null)) e20
        ,count(if(month(submit_time)<7 and year(submit_time)=2021,1,null)) e21
        from exam_record
        join examination_info using(exam_id)
        group by 1) a) b
where e20*e21 != 0
order by 4 desc,6 desc;

with t as (
    select
        tag,
        count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) exam_cnt_20,
        count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) exam_cnt_21,
        rank() over(order by count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_20,
        rank() over(order by count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_21
    from examination_info ei 
    join exam_record er
    on ei.exam_id = er.exam_id
    group by tag
)
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round((exam_cnt_21 - exam_cnt_20) * 100 / exam_cnt_20,1),'%') growth_rate,
    exam_cnt_rank_20,
    exam_cnt_rank_21,
    cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) rank_delta
from t
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc,exam_cnt_rank_21 desc;

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select tag,
       exam_cnt_20,
			 exam_cnt_21,
			 concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
			 exam_cnt_rank_20,
			 exam_cnt_rank_21,
			 cast(exam_cnt_rank_21 as signed )-cast(exam_cnt_rank_20 as signed) rank_deIta
from (
			select tag,
			       count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) exam_cnt_20,
			       count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) exam_cnt_21, 
			       rank() over(order by count(if(date_format(start_time,'%Y%md') between '20200101' and '20200630',start_time,null)) desc) exam_cnt_rank_20,
			       rank() over(order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) exam_cnt_rank_21 
			from exam_record 
			join examination_info using(exam_id)
			where score is not null  
			group by tag 
									) t 
where exam_cnt_20*exam_cnt_21 <>0 
order by 4 desc,6 desc  

select 
tag,
exam_cnt_20,
exam_cnt_21,
concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,
exam_cnt_rank_20,
exam_cnt_rank_21,
cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta
from 
(select 
tag,
count(if(year(start_time)='2020'and month(start_time) between 1 and 6,score,null)) exam_cnt_20,
count(if(year(start_time)='2021'and month(start_time)<7,score,null)) exam_cnt_21,
rank()over(order by (count(if(year(start_time)='2020' and month(start_time)<7,score,null))) desc) exam_cnt_rank_20,
rank()over(order by (count(if(year(start_time)='2021' and month(start_time)<7,score,null))) desc) exam_cnt_rank_21
from examination_info
join exam_record
using(exam_id)
where score is not null
group by 1) t 
where exam_cnt_20* exam_cnt_21!=0
order by 4 desc,6 desc