题解 | #月均完成试卷数不小于3的用户爱作答的类别#

题目：请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出

条件分解：

• 当月均完成试卷数”不小于3的用户
• 用户们爱作答的类别及作答次数
• 按次数降序输出

思路：

第一步：取出月均完成试卷数不小于3的用户uid，这里要注意是月均，不仅要根据uid进行分组还要根据月进行分组

    select
      uid
    from
      exam_record
    group by
      uid,
      date_format(start_time,'%Y-%m')
    having
      sum(if (submit_time is not null, 1, 0)) >= 3

第二步：根据exam_record 和 examination_info两个表关联，取出符合条件用户的记录，并根据试卷类型tag进行分组聚合统计

完整代码：

select
  a2.tag,
  count(*) tag_cnt
from
  exam_record a1
  left join examination_info a2 on a1.exam_id = a2.exam_id
where
  a1.uid in (
    select
      uid
    from
      exam_record
    group by
      uid,
      date_format(start_time,'%Y-%m')
    having
      sum(if (submit_time is not null, 1, 0)) >= 3
  )
group by
  a2.tag
order by
  tag_cnt desc