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

明确题意：

统计月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出

问题拆解：

• 筛选完成了的试卷的记录。知识点：where
• 筛选月均完成数不小于3的用户。知识点：
  • 按用户分组group by uid；
  • 统计当前用户完成试卷总数count(exam_id)；
  • 统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, "%Y%m"))；
  • 分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3；
• 关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
• 筛选满足条件的用户。知识点：where uid in (...)
• 统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
• 按次数降序输出。知识点：order by tag_cnt desc

代码实现：

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