SQL20 月均完成试卷数不小于3的用户爱作答的类别

题目主要信息：

• 从表中统计出月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出
• 其中试卷信息记录在表examination_info（包括试卷ID、类别、难度、时长、发布时间），答题信息记录在表exam_record（包括试卷ID、用户ID、开始时间、结束时间、得分）

问题拆分：

• 筛选出月均完成试卷数不少于3的用户uid：
  • 根据uid做分组，便于统计每组的月均完成试卷数。知识点：group by
  • 从exam_record中筛选出uid作为分组，得分栏不为空（代表是完成的试卷），对每个组再单独判断月均完成数。知识点：select...from...where...
  • 根据uid分组统计每个人在上述得分栏不为空的情况下的exam_id，即每个人完成的试卷总数，然后除以这个人做题的月份，即分组情况下的统计不同的月份出现了多少次，每个组都要这个商大于等于3。having count(exam_id) / count(distinct date_format(submit_time, "%Y%m")) >= 3 知识点：having、count()、distinct、date_format()
• 从上述uid中选出他们做的题的标签和每种标签数量：
  • 根据标签tag做分组，便于统计每种标签的试卷完成数。知识点：group by
  • 标签信息在exam_record中，而完成信息在examination_info中，因此要将两个表依靠exam_id连接在一起。知识点：join...on...
  • 每个组以uid在上面select出的结果中的情况下，统计exam_id的数量，统计每个组被这些人做完的试卷的份数，这里不要求distinct，因为试卷可以重复做。知识点：count
• 根据标签数量降序输出。知识点：order by ... desc

代码：

select tag, count(exam_record.exam_id) as tag_cnt
from exam_record join examination_info
on exam_record.exam_id = examination_info.exam_id
where uid in (
    select uid
    from exam_record
    where score is not null 
    group by uid
    having count(exam_id) / count(distinct date_format(submit_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc