【牛客】SQL130 试卷发布当天作答人数和平均分

描述

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间），示例数据如下：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	3100	7	算法	2020-01-01 10:00:00
2	1002	牛客2号	2100	6	算法	2020-01-01 10:00:00
3	1003	牛客3号	1500	5	算法	2020-01-01 10:00:00
4	1004	牛客4号	1100	4	算法	2020-01-01 10:00:00
5	1005	牛客5号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客6号	3000	6	C++	2020-01-01 10:00:00

释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间） 示例数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 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	2021-07-02 09:01:01	2021-09-01 09:41:01	70
2	1002	9003	2021-09-01 12:01:01	2021-09-01 12:21:01	60
3	1002	9002	2021-09-02 12:01:01	2021-09-02 12:31:01	70
4	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	80
5	1002	9003	2021-08-01 12:01:01	2021-08-01 12:21:01	60
6	1002	9002	2021-08-02 12:01:01	2021-08-02 12:31:01	70
7	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	85
8	1002	9002	2021-07-06 12:01:01	(NULL)	(NULL)
9	1003	9002	2021-09-07 10:01:01	2021-09-07 10:31:01	86
10	1003	9003	2021-09-08 12:01:01	2021-09-08 12:11:01	40
11	1003	9003	2021-09-01 13:01:01	2021-09-01 13:41:01	70
12	1003	9001	2021-09-08 14:01:01	(NULL)	(NULL)
13	1003	9002	2021-09-08 15:01:01	(NULL)	(NULL)
14	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:01	90
15	1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	88
16	1005	9002	2021-09-02 12:11:01	2021-09-02 12:31:01	89

请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：

exam_id	uv	avg_score
9001	3	81.3

解释：只有一张SQL类别的试卷，试卷ID为9001，发布当天（2021-09-01）有1001、1002、1003、1005作答过，但是1003是5级用户，其他3位为5级以上，他们三的得分有[70,80,85,90]，平均分为81.3（保留1位小数）。

select
exam_id,count(distinct uid) as uv,
round(avg(score),1) as avg_score
from
exam_record left join examination_info using (exam_id)
left join user_info using (uid)
where level>5 and submit_time is not null
and date_format(release_time,'%Y%m')=date_format(submit_time,'%Y%m')
group by exam_id
order by uv desc,avg_score