列表

详情

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位小数）。

示例1

输入: 

drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 1500, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1100, 4, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2020-02-01 10:00:00'),
  (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 70),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:21:01', 60),
(1002, 9002, '2021-09-02 12:01:01', '2021-09-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
(1002, 9003, '2021-08-01 12:01:01', '2021-08-01 12:21:01', 60),
(1002, 9002, '2021-08-02 12:01:01', '2021-08-02 12:31:01', 70),
(1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
(1002, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9001, '2021-09-01 13:01:01', '2021-09-01 13:41:01', 70),
(1003, 9002, '2021-09-08 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 90),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

输出: 

9001|3|81.3

原站题解

上次编辑到这里，代码来自缓存 点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6396KB, 提交时间: 2022-01-25 

SELECT
    exam_id,
    count( DISTINCT uid ) AS uv,
    ROUND(avg( score ), 1) AS avg_score
FROM exam_record
WHERE (exam_id, DATE(start_time)) IN (
    SELECT exam_id, DATE(release_time)
    FROM examination_info WHERE tag = "SQL"
) AND uid IN ( SELECT uid FROM user_info WHERE `level` > 5 )
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6376KB, 提交时间: 2022-01-25 

select ei.exam_id,count(distinct ui.uid) as uv,ROUND(AVG(er.score),1) as avg_score
from user_info ui
join exam_record er
on ui.uid =er.uid
join examination_info ei
on er.exam_id = ei.exam_id
where ui.level>5 and date(er.start_time)=date(ei.release_time) and tag='SQL'
group by ei.exam_id
order by uv desc,avg_score asc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-06 

select a.exam_id, count(distinct a.uid) as uv, round(avg(score),1) as avg_score from
exam_record as a left join examination_info as b
on a.exam_id=b.exam_id 
left join user_info as c 
on a.uid=c.uid
where c.level>5 and date(b.release_time)=date(a.submit_time) and tag='SQL'
group by exam_id, date(submit_time)
order by uv desc, avg_score asc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-11-30 

 SELECT 
exam_id,
COUNT(DISTINCT a.uid) AS uv,
ROUND(AVG(score),1) AS avg_score 
 FROM
 (
 SELECT b.*,c.tag FROM `user_info` a 
 LEFT JOIN `exam_record` b  ON a.uid = b.uid  AND a.`level` > 5 AND b.submit_time LIKE '2021-09-01%'
 LEFT JOIN `examination_info` c ON b.exam_id = c.exam_id AND c.tag = 'SQL'
) a
WHERE a.tag = 'SQL' GROUP BY exam_id ORDER BY uv DESC,avg_score

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2021-12-17 

select er.exam_id,count(distinct er.uid)as uv,round(avg(er.score),1)as avg_score
from exam_record er
join examination_info  ei using(exam_id)
join user_info ui using (uid)
where date_format(er.start_time,'%Y%m%d')=date_format(ei.release_time,'%Y%m%d')
and ui.level>5
group by er.exam_id
order by uv desc,avg_score asc

上一题

© 2024 nowcoder.com