【牛客】SQL141 试卷完成数同比2020年的增长率及排名变化-窗口函数

描述

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-01-01 10:00:00
2	9002	C++	hard	80	2021-01-01 10:00:00
3	9003	算法	hard	80	2021-01-01 10:00:00
4	9004	PYTHON	medium	70	2021-01-01 10:00:00

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-08-02 10:01:01	2020-08-02 10:31:01	89
2	1002	9001	2020-04-01 18:01:01	2020-04-01 18:59:02	90
3	1001	9001	2020-04-01 09:01:01	2020-04-01 09:21:59	80
5	1002	9001	2021-03-02 19:01:01	2021-03-02 19:32:00	20
8	1003	9001	2021-05-02 12:01:01	2021-05-02 12:31:01	98
13	1003	9001	2020-01-02 10:01:01	2020-01-02 10:31:01	89
9	1001	9002	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1002	9002	2021-02-02 12:01:01	2020-02-02 12:43:01	81
11	1001	9002	2020-01-02 19:01:01	2020-01-02 19:59:01	69
16	1002	9002	2020-02-02 12:01:01
17	1002	9002	2020-03-02 12:11:01
18	1001	9002	2021-05-05 18:01:01
4	1002	9003	2021-01-20 10:01:01	2021-01-20 10:10:01	81
6	1001	9003	2021-04-02 19:01:01	2021-04-02 19:40:01	89
15	1002	9003	2021-01-01 18:01:01	2021-01-01 18:59:02	90
7	1004	9004	2020-05-02 12:01:01	2020-05-02 12:20:01	99
12	1001	9004	2021-09-02 12:11:01
14	1002	9004	2020-01-01 12:11:01	2020-01-01 12:31:01	83

请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

由示例数据结果输出如下：

tag	exam_cnt_20	exam_cnt_21	growth_rate	exam_cnt_rank_20	exam_cnt_rank_21	rank_delta
SQL	3	2	-33.3%	1	2	1

解释：2020年上半年有3个tag有作答完成的记录，分别是C++、SQL、PYTHON，它们被做完的次数分别是3、3、2，做完次数排名为1、1（并列）、3；

2021年上半年有2个tag有作答完成的记录，分别是算法、SQL，它们被做完的次数分别是3、2，做完次数排名为1、2；具体如下：

tag	start_year	exam_cnt	exam_cnt_rank
C++	2020	3	1
SQL	2020	3	1
PYTHON	2020	2	3
算法	2021	3	1
SQL	2021	2	2

因此能输出同比结果的tag只有SQL，从2020到2021年，做完次数3=>2，减少33.3%（保留1位小数）；排名1=>2，后退1名。

知识点：数据格式转换

• CAST(字段名 AS 格式类型 )

• SQL常用的格式类型
  • 二进制：BINARY    
  • 字符型: CHAR
  • 日期 : DATE     
  • 时间: TIME     
  • 日期时间型 : DATETIME     
  • 浮点数 : DECIMAL      
  • 整数 : SIGNED     
  • 无符号整数 : UNSIGNED

with cte as(
select
tag,year(start_time) as start_year,
count(exam_id) as exam_cnt,
rank() over(partition by year(start_time) order by count(exam_id) desc) as exam_cnt_rank
from
exam_record left join examination_info using(exam_id)
where submit_time is not null and month(start_time)<=6
group by tag,year(start_time)
)

select
t1.tag as tag,
t1.exam_cnt as exam_cnt_20,t2.exam_cnt as exam_cnt_21,
concat(round(((t2.exam_cnt-t1.exam_cnt)/t1.exam_cnt)*100,1),'%') as growth_rate,
t1.exam_cnt_rank as exam_cnt_rank_20,t2.exam_cnt_rank as exam_cnt_rank_21,
cast(t2.exam_cnt_rank as signed)-cast(t1.exam_cnt_rank as signed) as rank_delta
from
cte t1 left join cte t2
on t1.tag=t2.tag and t1.start_year+1=t2.start_year
where t2.exam_cnt is not null
order by growth_rate desc,exam_cnt_rank_21 desc