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

题目主要信息：

  • 找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名
  • 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

问题拆分：

  • 先从表exam_record中筛选出用户ID、答题开始时间、得分以及月份的降序排列：
    • 用户ID、开始时间、得分可以直接获取。
    • 月份降序我们用分组连续排名。知识点：dense_rank() over()、date_format() 对每个用户ID内进行排名，因为一个月可能出现多次，所以要采用连续排名，月份大的在前面月份小的在后，符合离现在最近的月份在前。dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
  • 从上述结果中筛选出每个用户近三个的答题数：
    • 对于每个用户进行筛选，因此要以uid分组。知识点：group by
    • 只筛出近三个月的内容，因此上述排名我们只要排名小于等于3的。知识点：where
    • 过滤掉未完成试卷的用户，需要再分组后判断每组用户ID出现次数和得分出现次数是否一致，因为有得分才代表完成了试卷。知识点：having、count()
    • 统计上述没有过滤掉的结果中，每人的得分的总数，代表完成了多少试卷。知识点：count()
  • 根据先答题数后用户ID的降序次序输出。order by exam_complete_cnt desc, uid desc 知识点：order by...desc

代码：

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