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

# 子查询：选出“当月均完成试卷数” 不小于3的用户
# 按用户、月份分组，选出用户每个月的作答次数 都大于2 的用户
# 外层查询：统计类别及作答次数

select
    tag,
    count(1) as tag_cnt
from
    exam_record
    left join examination_info using (exam_id)
where
    uid in (
        select
            uid
        from
            exam_record
        where
            submit_time is not null
        group by
            uid,DATE_FORMAT (submit_time, "%Y%M")
        having
           count(submit_time) > 2
    )
group by
    tag
order by
    tag_cnt desc