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

题目难点：

1、总活动月数，并非21年活动月数；(两种时间的统计方式）

2、试卷并不一定包含试题，两项是独立的；(两个表的拼接）

3、每个用户，left join；(左连接+聚合操作）

思路如下：

1、首先整理数据：分别筛选 满足条件用户的试卷情况和试题情况，并进行数据拼接

涉及到的函数：union all-数据拼接；where满足条件用户；left join-试卷情况

这里需要注意一下，on和where对于条件处理的先后顺序

2、聚合操作：统计每个用户的数据，必然是聚合操作group by

3、统计条件：条件判断+统计

涉及到的函数：substr-日期格式判断；if-条件判断；distinct-去重；count-计数

union all搭配标签建立一维表 + left join + group by + case when/if搭配 count统计

with t1 as (
    select
        ui.uid
        ,er.start_time as ti #只需要时间这个字段就可以
        ,'exam' as tag #需要标签进行区分试卷还是试题
    from user_info ui left join exam_record er 
    on ui.uid=er.uid 
    where ui.level='6' or ui.level='7' #注意条件写法
    union all
    select
        ui.uid
        ,pr.submit_time as ti #只需要时间这个字段就可以
        ,'question' as tag
    from user_info ui left join practice_record pr 
    on ui.uid=pr.uid
    where ui.level='6' or ui.level='7'  #注意条件写法
)
select
    t1.uid
    ,count(distinct substr(t1.ti,1,7)) as act_month_total
    ,count(distinct if(substr(t1.ti,1,10) between '2021-01-01' and '2021-12-31',substr(t1.ti,1,10),null)) as act_days_2021
    ,count(distinct if(substr(t1.ti,1,10) between '2021-01-01' and '2021-12-31' and t1.tag='exam',substr(t1.ti,1,10),null)) as act_days_2021_exam
    ,count(distinct if(substr(t1.ti,1,10) between '2021-01-01' and '2021-12-31' and t1.tag='question',substr(t1.ti,1,10),null)) as act_days_2021_question
from t1
group by t1.uid
order by act_month_total desc
        ,act_days_2021 desc