SQL25 满足条件的用户的试卷完成数和题目练习数

题目主要信息：

• 请找到高难度SQL试卷得分平均值大于80并且是7级红名大佬的2021年试卷完成数和题目练习数
• 用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
• 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
• 题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）

问题拆分：

• 先找满足2021年高难度试卷平均得分大于80且是7级红名大佬的用户ID：
  • 我们要找到每个用户ID满足上述条件，其中平均分肯定要以uid分组。知识点：group by
  • 考试得分及提交时间在表exam_record中，试卷信息在表examination_info中，用户信息在表user_info中，我们通过exam_id将前二者连接，再通过uid将用户信息表连在后面。知识点：join...on...
  • 从连接后表中选出符合条件的ID。where tag = 'SQL' and difficulty = 'hard' and year(submit_time) = 2021 and level = 7
  • 同时还需要判断平均分，筛选出平均分大于80的。知识点：having、avg()
• 再从上述ID中找到每个用户2021年试卷答题数：
  • 从表exam_record中筛选出用户ID在上面筛选出的结果里，且提交时间是2021年的用户ID和每个ID的作答场次。知识点：select...from...where...in...and...
  • 求每个ID的作答场次需要以uid为划分进行分组。知识点：group by
  • 最后将结果命名为一个新表。
• 最后将找到每个用户的2021年试卷答题数和练习题数：
  • 使用left join将新表与practice_record连接，因为筛选出来的新表一定做了试卷，但是不一定做了练习题，left join可以让新表连接全部，practice_record中没有的连接null。 知识点：left join...on...
  • 从连接后的表中选出用户ID、每个ID的作答场次、每个ID的练习题目数，需要以uid分组。知识点：group by
  • 对每组的作答场次直接选择，练习题数目筛选提交时间是2021年，或者为空(0题）的进行计数。where year(submit_time) = 2021 or submit_time is null 知识点：count()
• 按照试卷答题数升序、练习题数降序输出。order by exam_cnt asc, question_cnt desc

代码：

select exam.uid as uid,
       exam_cnt,
       count(score) as question_cnt
from
    (select uid,
            count(score) as exam_cnt
    from exam_record
    where uid in(
                select  e_r.uid as uid
                from exam_record e_r join examination_info e_i
                on e_r.exam_id = e_i.exam_id
                join user_info u_i
                on e_r.uid = u_i.uid
                where tag = 'SQL'
                and difficulty = 'hard'
                and year(submit_time) = 2021
                and level = 7
                group by e_r.uid
                having avg(score) > 80
                )
    and year(submit_time) = 2021
    group by uid) exam
left join practice_record p_r
on exam.uid = p_r.uid
where year(submit_time) = 2021 or submit_time is null
group by uid
order by exam_cnt asc, question_cnt desc