牛客网SQL进阶134： 满足条件的用户的试卷总完成次数和题目总练习次数

满足条件的用户的试卷完成数和题目练习数_牛客题霸_牛客网

0 问题描述

  基于用户信息表user_info、试卷信息表examination_info、试卷作答记录表exam_record、题目练习记录表practice_record，筛选出 高难度SQL试卷得分平均值大于80并且是7级的用户，统计他们2021年试卷总完成次数和题目总练习次数，结果按试卷完成数升序，按题目练习数降序。

1 数据准备

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号', 2000, 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++', 'hard', 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),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(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),
(1004, 8002, '2021-08-02 19:58:01', 94),
(1004, 8003, '2021-08-02 19:38:01', 70),
(1004, 8003, '2021-08-02 19:48:01', 90),
(1004, 8003, '2021-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', 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, 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:01', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);

2 数据分析

select 
     t1.uid,
     count(distinct case when year(t2.submit_time) = '2021' then t2.id else null end) as exam_cnt, 
     count(distinct case when year(t3.submit_time) = '2021' then t3.id else null end) as question_cnt 
from 
   (
   	  select 
   	       uid
   	  from exam_record where uid in (select uid from user_info where level  = 7 ) 
   	        and exam_id in (select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard')
   	  group by uid 
   	  having sum(score) / count(score) > 80 
   ) t1 
left join exam_record t2 
 on t1.uid = t2.uid 
left join practice_record t3 
 on t1.uid = t3.uid 
  group by t1.uid
   order by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序，按题目练习数降序

思路分析：

• step1: 先筛选出 平均值大于80并且是7级用户的uid，得到t1
• step2：t1分别与t2、t3关联，要用left join，因为有些uid可能没做某个试卷或练习，也要保留记录
• step3：count(distinct )时，以id区分(case when ..then id )，不能以exam_id区分，因为存在一个uid可能对同一个试卷或练习做过多次。

3 小结