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

步骤拆解：

1. 统计每个用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数；
2. 筛选出其中6/7级用户（不管期间是否活跃）；

第一步，考虑到需要统计用户的总活跃数据，分别做连接比较复杂，直接用union all将exam_record和practice_record两表做合并作为新表e；

with e as 
(select uid,start_time,date(start_time) as act_date,
    date_format(start_time,'%Y%m') as act_month,'exam' as type
from exam_record
union all
select uid,submit_time,date(submit_time),
    date_format(submit_time,'%Y%m'),'practice'
from practice_record)

第二步，分组统计用户的活跃信息，数据来源于e表，主要使用count、distinct、case when几个知识点，但是需要统计所有6/7级用户，哪怕没有周期内的活跃信息，所以这一步使用user_info为主表，和e表做left join;

1. 用户总活跃月份数:count(distinct e.act_month) as act_month_total
2. 2021年活跃天数:count(distinct CASE when year(e.start_time)=2021 then e.act_date else null end) as act_days_2021;
3. 2021年试卷作答活跃天数:count(distinct CASE when year(e.start_time)=2021 and e.type='exam' then e.act_date else null end) as act_days_2021_exam;
4. 2021年答题活跃天数:count(distinct CASE when year(e.start_time)=2021 and e.type='practice' then e.act_date else null end) as act_days_2021_question;

第三步，筛选6/7级的用户，作为子查询条件；

select ui.uid 
from user_info as ui
where ui.uid in (
          select uid from user_info
                  where level in (6,7))

以下为最终代码：

with e as 
(select uid,start_time,date(start_time) as act_date,
    date_format(start_time,'%Y%m') as act_month,'exam' as type
from exam_record
union all
select uid,submit_time,date(submit_time),
    date_format(submit_time,'%Y%m'),'practice'
from practice_record)
select
ui.uid,count(distinct e.act_month) as act_month_total,
count(distinct CASE when year(e.start_time)=2021 
      then e.act_date else null end) as act_days_2021,
count(distinct CASE when year(e.start_time)=2021
                          and e.type='exam'
      then e.act_date else null end) as act_days_2021_exam,
count(distinct CASE when year(e.start_time)=2021
                          and e.type='practice'
      then e.act_date else null end) as act_days_2021_question
from user_info as ui
left join e
on ui.uid=e.uid
where ui.uid in (
        select uid from user_info
        where level in (6,7))
group by ui.uid
order by act_month_total desc,act_days_2021 desc;