描述
现有用户信息表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 |
试卷信息表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 | 2021-09-01 06:00:00 |
3 | 9003 | 算法 | medium | 80 | 2021-09-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 | 2021-09-01 09:01:01 | 2021-09-01 09:41:01 | 79 |
2 | 1002 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:21:01 | 60 |
3 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 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-09-01 12:01:01 | 2021-09-01 12:31:01 | 70 |
7 | 1002 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 85 |
8 | 1002 | 9002 | 2021-09-01 12:01:01 | (NULL) | (NULL) |
9 | 1003 | 9003 | 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 | 9001 | 2021-09-01 13:01:01 | 2021-09-01 13:41:01 | 81 |
12 | 1003 | 9002 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
13 | 1003 | 9003 | 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 | 1006 | 9002 | 2021-09-01 12:11:01 | 2021-09-01 12:31:01 | 89 |
统计作答SQL类别的试卷得分大于过80的人的用户等级分布,按数量降序排序(保证数量都不同)。示例数据结果输出如下:
level | level_cnt |
6 | 2 |
5 | 1 |
解释:9001为SQL类试卷,作答该试卷大于80分的人有1002、1003、1005共3人,6级两人,5级一人。
select
level,count(distinct uid) as level_cnt
from
exam_record left join examination_info using(exam_id)
left join user_info using(uid)
where score is not null and score>80 and exam_id=9001
group by level
order by level_cnt desc,level desc