题解 | 每个6/7级用户活跃情况

# 粒度-用户
# 筛选符合条件的用户uid, 进行各各查询然后表连接
with tmp as 
(
    select
    uid
    from
    user_info
    where level > 5
)
,
tmp1 as 
(
    select
    uid,
    submit_time act_time
    from
    exam_record
    union all
    select
    uid,
    submit_time act_time
    from
    practice_record
)   
,
month_cnt as 
(
    select
    tmp.uid
    ,count(distinct date_format(act_time, '%Y-%m')) act_month_total
    from 
    tmp
    left join
    tmp1
    on tmp.uid = tmp1.uid
    group by tmp.uid
)
,
days_cnt as 
(
    select
    tmp.uid
    ,count(distinct date(act_time)) act_days_2021
    from 
    tmp
    left join
    tmp1
    on tmp.uid = tmp1.uid
    and year(act_time) = 2021
    group by tmp.uid
)
,
exams_cnt as 
(
    select
    tmp.uid
    ,count(distinct date(submit_time)) act_days_2021_exam
    from 
    tmp
    left join
    exam_record er
    on tmp.uid = er.uid
    where year(submit_time) = 2021
    group by tmp.uid
)
,
questions_cnt as 
(
    select
    tmp.uid
    ,count(distinct date(submit_time)) act_days_2021_question
    from 
    tmp
    left join
    practice_record pr
    on tmp.uid = pr.uid
    where year(submit_time) = 2021
    group by tmp.uid
)

select
mc.uid
,act_month_total
,(case when act_days_2021 is not null then act_days_2021
    else 0 end) act_days_2021
,(case when act_days_2021_exam is not null then act_days_2021_exam
    else 0 end) act_days_2021_exam
,(case when act_days_2021_question is not null then act_days_2021_question
    else 0 end) act_days_2021_question
from
month_cnt mc
left join
days_cnt dc
on mc.uid = dc.uid
left join
exams_cnt ec
on mc.uid = ec.uid
left join 
questions_cnt qc
on mc.uid = qc.uid
order by act_month_total desc, act_days_2021 desc;

# 步骤：1.筛选出复合条件用户uid;2.分表求出各部分数据；3.表连接
# 注意点(1):需要保证符合条件的用户全部保留，故需要用left join
# 注意点(2):有的用户没有活跃天数，需要用case when 函数赋值为0