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

/*
--1.每个用户的总作答次数(聚合uid)
--2.选出月均作答次数大于2的用户id（where语句筛选；用distinct去重，筛选出答题的月份；最后用count统计答题次数和答题的月数）
--3.join表格得到这些用户的作答类型和作答次数，
--4.根据作答类型聚合最终结果，按照作答次数降序排序
 */
with
    tem as (
        select
            uid,
            count(score)/count(distinct month(submit_time)) nums
        from
            exam_record
        where
            score is not null
        group by
            uid
        having
            nums > 2
    )
select
    ei.tag as tag,
    count(*) as tag_cnt
from
    tem
    left join exam_record as er on tem.uid = er.uid
    left join examination_info as ei on er.exam_id = ei.exam_id
group by
    ei.tag
order by
    tag_cnt desc;