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

select
    tag, count(uid) as tag_cnt
from
    examination_info i
    inner join exam_record r using (exam_id)
where
    uid in (
        select
            uid
        from
            exam_record
        where
            date_format (start_time, '%Y%m') = date_format (submit_time, '%Y%m')
            and submit_time is not null
        # 这里要按照month分一次组，否则一个用户1月作答次数大于3，2月作答次数小于3再tag_cnt统计上会有问题
        group by uid, month(start_time)
        # 可以通过having做聚合后的判断，且不需要再select中体现
        having count(uid)>=3
    )
group by tag
order by count(tag) desc