SQL22 作答试卷得分大于过80的人的用户等级分布

题目主要信息：

  • 统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序，相同数量按照等级降序
  • 用户信息表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得分）

问题拆分：

  • 以每个等级分组，即level作为分组依据，便于计算每个等级的符合条件的人数。知识点：group by
  • 对于每个等级，我们只挑选类别为SQL、且得分大于80的不同的用户进行统计人数，相同的用户做了多次只统计一次：
    • 上述两个要求加上分组的等级分布在三个表中，我们可以将exam_record表根据exam_id与examination_info表连接在一起，然后将exam_record表根据uid与user_info连接在一起，这样三个表就连结在一起了。知识点：join...on...
    • 用where语句判断上述两种情况。知识点：where
  • 按照人数的降序，相同情况下等级降序输出。order by level_cnt desc, level desc 知识点：order by

代码：

select level,
       count(distinct u_i.uid) as level_cnt
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 score > 80
group by level 
order by level_cnt desc, level desc