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

# count不会将空值记录进去
# 计算月均完成试卷数，等于总完成试卷数/多少各月份
# t1先计算总的完成数量和总共占多少月份
with t1 as (
    select uid, 
    count(score) as total_cnt,
    count(distinct DATE_FORMAT(start_time, "%Y%m")) as month_cnt
    from exam_record 
    where score IS NOT NULL
    group by uid
),
# 找到月均完成数满足条件的用户
t2 as (
    select uid, 
    (case when month_cnt = 0 then total_cnt
    else total_cnt/month_cnt end) as month_avg
    from t1
    having month_avg >= 3
),
t3 as (
    select uid, exam_id
    from exam_record as a
    where uid in (
        select uid
        from t2
    )
)
select b.tag, count(t3.exam_id) as tag_cnt
from t3
left join examination_info as b
on t3.exam_id = b.exam_id
group by b.tag
order by tag_cnt desc;