SQL 进阶挑战（26 - 30）

村雨遥

发布于 2022-06-27 09:18:21

4130

发布于 2022-06-27 09:18:21

文章被收录于专栏：JavaPark

SQL26 每个6/7级用户活跃情况

描述

现有用户信息表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号	2300	7	算法	2020-01-01 10:00:00
3	1003	牛客3号	2500	7	算法	2020-01-01 10:00:00
4	1004	牛客4号	1200	5	算法	2020-01-01 10:00:00
5	1005	牛客5号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客6号	2600	7	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得分）：

uid	exam_id	start_time	submit_time	score
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	78
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
1005	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:02	85
1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:59	84
1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	81
1002	9001	2020-09-01 13:01:01	2020-09-01 13:41:01	81
1005	9001	2021-09-01 14:01:01	(NULL)	(NULL)

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

uid	question_id	submit_time	score
1001	8001	2021-08-02 11:41:01	60
1004	8001	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1001	8002	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1006	8002	2021-08-04 19:58:01	94
1006	8003	2021-08-03 19:38:01	70
1006	8003	2021-08-02 19:48:01	90
1006	8003	2020-08-01 19:38:01	80

问题

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：

uid	act_month_total	act_days_2021	act_days_2021_exam	act_days_2021_question
1006	3	4	1	3
1001	2	2	1	1
1005	1	1	1	0
1002	1	0	0	0
1003	0	0	0	0

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

示例

输入：
drop table if exists examination_info,user_info,exam_record,practice_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 practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint 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号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2600, 7, '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, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-08-01 19:38:01', 80);

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:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1005, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);
输出：
1006|3|4|1|3
1001|2|2|1|1
1005|1|1|1|0
1002|1|0|0|0
1003|0|0|0|0

解答

SELECT 
    ui.uid,
    COUNT(DISTINCT LEFT(s,6)) AS act_month_total,
    COUNT(DISTINCT IF(LEFT(s,4)='2021',RIGHT(s,4),null)) AS act_days_2021,
    COUNT(DISTINCT IF(LEFT(s,4)='2021' AND tag='e',RIGHT(s,4),null)) AS act_days_2021_exam,
    COUNT(DISTINCT IF(LEFT(s,4)='2021' AND tag='p',RIGHT(s,4),null)) AS act_days_2021_question
FROM (
        SELECT uid,DATE_FORMAT(submit_time,'%Y%m%d') AS s,'p' tag FROM practice_record pr
        UNION ALL
        SELECT uid,DATE_FORMAT(start_time,'%Y%m%d') AS s,'e' AS tag FROM exam_record er 
)mon
RIGHT JOIN user_info ui
ON ui.uid = mon.uid
WHERE ui.level >5
GROUP BY uid
ORDER BY act_month_total DESC,act_days_2021 DESC;

SQL27 每类试卷得分前3名

描述

现有试卷信息表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	SQL	hard	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:31:00	78
2	1002	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
3	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	81
4	1003	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	86
5	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:51	89
6	1004	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
7	1005	9003	2021-09-01 12:01:01	2021-09-01 12:31:02	85
8	1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:01	84
9	1003	9003	2021-09-08 12:01:01	2021-09-08 12:11:01	40
10	1003	9002	2021-09-01 14:01:01	(NULL)	(NULL)

问题

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。由示例数据结果输出如下：

tid	uid	ranking
SQL	1003	1
SQL	1004	2
SQL	1002	3
算法	1005	1
算法	1006	2
算法	1003	3

解释：有作答得分记录的试卷tag有SQL和算法，SQL试卷用户1001、1002、1003、1004有作答得分，最高得分分别为81、81、89、85，最低得分分别为78、81、86、40，因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。

示例

输入：
drop table if exists examination_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 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 examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 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:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);
输出：
SQL|1003|1
SQL|1004|2
SQL|1002|3
算法|1005|1
算法|1006|2
算法|1003|3

解答

首先筛选出一个各类标签与用户和排名的结果集。

SELECT tag, exam_record.uid,
    row_number() over (PARTITION BY tag ORDER BY tag, 
                       MAX(score) DESC, 
                       MIN(score) DESC, exam_record.uid DESC) ranking
    FROM exam_record JOIN examination_info
    ON exam_record.exam_id = examination_info.exam_id
    GROUP BY tag, exam_record.uid;

然后就是从结果集中选出排名不大于 3 的标签、用户 ID 和排名，即 SELECT ... FROM ... WHERE...。

SELECT tag, uid, ranking
FROM(
    SELECT tag, exam_record.uid,
    row_number() over (PARTITION BY tag ORDER BY tag, 
                       MAX(score) DESC, 
                       MIN(score) DESC, exam_record.uid DESC) ranking
    FROM exam_record JOIN examination_info
    ON exam_record.exam_id = examination_info.exam_id
    GROUP BY tag, exam_record.uid
) ranktable
WHERE ranking <= 3;

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

描述

现有试卷信息表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++	hard	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:51:01	78
2	1001	9002	2021-09-01 09:01:01	2021-09-01 09:31:00	81
3	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	81
4	1003	9001	2021-09-01 19:01:01	2021-09-01 19:59:01	86
5	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:51	89
6	1004	9002	2021-09-01 19:01:01	2021-09-01 19:30:01	85
7	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:02	85
8	1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	84
9	1003	9001	2021-09-08 12:01:01	2021-09-08 12:11:01	40
10	1003	9002	2021-09-01 14:01:01	(NULL)	(NULL)
11	1005	9001	2021-09-01 14:01:01	(NULL)	(NULL)
12	1003	9003	2021-09-08 15:01:01	(NULL)	(NULL)

问题

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。由示例数据结果输出如下：

exam_id	duration	release_time
9001	60	2021-09-01 06:00:00

解释：试卷9001被作答用时有50分钟、50分钟、30分1秒、11分钟、10分钟，第二快和第二慢用时之差为50分钟-11分钟=39分钟，试卷时长为60分钟，因此满足大于试卷时长一半的条件，输出试卷ID、时长、发布时间。

示例

输入：
drop table if exists examination_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 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 examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 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:51:01', 78),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);
输出：
9001|60|2021-09-01 06:00:00

解答

根据问题将题目进行拆解，首先将每套试卷完成第二快和完成第二慢的用时以及试卷信息统计出来。

SELECT exam_id, duration, release_time
FROM (
    SELECT DISTINCT exam_id, duration, release_time,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took DESC) as max2nd_time,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took ASC) as min2nd_time
    FROM (
        SELECT exam_id, duration, release_time,
            TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
        FROM exam_record JOIN examination_info USING(exam_id)
        WHERE submit_time IS NOT NULL
    ) as exam_record_timetook
) as exam_time_took;

然后筛选出第二快和第二慢用时之差大于试卷时长一半的试卷，即 WHERE max2nd_time - min2nd_time > duration / 2。

SELECT exam_id, duration, release_time
FROM (
    SELECT DISTINCT exam_id, duration, release_time,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took DESC) as max2nd_time,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took ASC) as min2nd_time
    FROM (
        SELECT exam_id, duration, release_time,
            TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
        FROM exam_record JOIN examination_info USING(exam_id)
        WHERE submit_time IS NOT NULL
    ) as exam_record_timetook
) as exam_time_took
WHERE max2nd_time - min2nd_time > duration / 2
ORDER BY exam_id DESC;

SQL29 连续两次作答试卷的最大时间窗

描述

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

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:02	84
2	1006	9001	2021-09-01 12:11:01	2021-09-01 12:31:01	89
3	1006	9002	2021-09-06 10:01:01	2021-09-06 10:21:01	81
4	1005	9002	2021-09-05 10:01:01	2021-09-05 10:21:01	81
5	1005	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81

问题

请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：

uid	days_window	avg_exam_cnt
1006	6	2.57

解释：用户1006分别在20210901、20210906、20210907作答过3次试卷，连续两次作答最大时间窗为6天（1号到6号），他1号到7号这7天里共做了3张试卷，平均每天3/7=0.428571张，那么6天里平均会做0.428571*6=2.57张试卷（保留两位小数）；

用户1005在20210905做了两张试卷，但是只有一天的作答记录，过滤掉。

示例

示例1
输入：
drop table if exists exam_record;
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);
输出：
1006|6|2.57

解答

先统计 2021 年每个人的总作答数、最早最晚相隔天数、最大连续作答间隔。

SELECT uid,
        COUNT(start_time) exam_cnt,
        DATEDIFF(MAX(start_time), MIN(start_time)) + 1 diff_days, 
        MAX(DATEDIFF(next_start_time, start_time)) + 1 days_window
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time) over(
                PARTITION BY uid ORDER BY start_time) next_start_time
        FROM exam_record
        WHERE YEAR(start_time)=2021
    ) exam_record_lead
    GROUP BY uid;

然后筛选出最早和最晚相隔大于 1 天的记录，即 WHERE diff_days > 1。
最后计算平均能做试卷的套数，即 ROUND(days_window * exam_cnt / diff_days, 2) avg_exam_cnt。

SELECT uid, days_window, ROUND(days_window * exam_cnt / diff_days, 2) avg_exam_cnt
FROM (
    SELECT uid,
        COUNT(start_time) exam_cnt,
        DATEDIFF(MAX(start_time), MIN(start_time)) + 1 diff_days, 
        MAX(DATEDIFF(next_start_time, start_time)) + 1 days_window
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time) over(
                PARTITION BY uid ORDER BY start_time) next_start_time
        FROM exam_record
        WHERE YEAR(start_time)=2021
    ) exam_record_lead
    GROUP BY uid
) exam_record
WHERE diff_days > 1
ORDER BY days_window DESC, avg_exam_cnt DESC;

SQL30 近三个月未完成试卷数为0的用户完成情况

描述

现有试卷作答记录表exam_record（uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间，为空的话则代表未完成, score:得分）：

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-06 10:01:01	2021-09-06 10:21:02	84
2	1006	9001	2021-08-02 12:11:01	2021-08-02 12:31:01	89
3	1006	9002	2021-06-06 10:01:01	2021-06-06 10:21:01	81
4	1006	9002	2021-05-06 10:01:01	2021-05-06 10:21:01	81
5	1006	9001	2021-05-01 12:01:01	(NULL)	(NULL)
6	1001	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81
7	1001	9003	2021-08-01 09:01:01	2021-08-01 09:51:11	78
8	1001	9002	2021-07-01 09:01:01	2021-07-01 09:31:00	81
9	1001	9002	2021-07-01 12:01:01	2021-07-01 12:31:01	81
10	1001	9002	2021-07-01 12:01:01	(NULL)	(NULL)

问题

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

uid	exam_complete_cnt
1006	3

解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

示例

输入：
drop table if exists exam_record;
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84),
(1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89),
(1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81),
(1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81),
(1006, 9001, '2021-05-01 12:01:01', null, null),
(1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78),
(1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 81),
(1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 81),
(1001, 9002, '2021-07-01 12:01:01', null, null);
输出：
1006|3

解答

问题拆解：

先从 exam_record 表中筛选出用户 ID、答题开始时间、得分，并按照月份的逆序排列。

SELECT uid, start_time, score,
           dense_rank() OVER(PARTITION BY uid ORDER BY date_format(start_time, '%Y%m') DESC;

紧接着，基于上一步中筛选出的结果，然后从中筛选出每个人近三个月的答题数，但要注意其中的限制条件。

SELECT uid,
       COUNT(score) AS exam_complete_cnt
FROM [结果集] WHERE recent_months <= 3;

最后按照答题数、用户 ID 降序输出，得到最终结果如下。

SELECT uid,
       COUNT(score) AS exam_complete_cnt
FROM(
    SELECT uid, start_time, score,
           dense_rank() OVER(PARTITION BY uid ORDER BY date_format(start_time, '%Y%m') DESC) 
    AS recent_months
    FROM exam_record
) recent_table
WHERE recent_months <= 3
GROUP BY uid
HAVING COUNT(score) = COUNT(uid)
ORDER BY exam_complete_cnt DESC, uid DESC;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-06-20，如有侵权请联系 cloudcommunity@tencent.com 删除

ide

sql

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

ide

sql

编程算法