一、明确需求

• 题目需求：

统计6，7级的用户的总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数；按总活跃月份数、2021年活跃天数降序排序

二、思路分解

1、用户等级表选择level in (6,7) 得表t1

2、表2
select count(distinct if（）)结合计算各列
from
(表t1左连试卷作答表，选择uid和start_time，新增标志列type='exam'）
union all
（表t1左连题目练习表，选择uid和submit_time,新增标志列type='practice')

3、按题目要求排序输出

三、代码实现

with 
t1 as
(select distinct uid
from user_info 
where level in (6,7))

, t2 as
(
select uid,count(distinct date_format(t,'%Y-%m')) act_month_total
        , count(distinct if(year(t)='2021',date_format(t,'%Y-%m-%d'),null)) act_days_2021
        , count(distinct if(year(t)='2021' and type='exam',date_format(t,'%Y-%m-%d'),null)) act_days_2021_exam
        , count(distinct if(year(t)='2021' and type='practice',date_format(t,'%Y-%m-%d'),null)) act_days_2021_question
from 
    (
    select a.uid,b.start_time t ,'exam' as type 
    from t1  a 
    left join exam_record b on a.uid = b.uid
    union all 
    select a.uid,c.submit_time t ,'practice' as type
    from t1  a 
    left join practice_record c on a.uid = c.uid
    )x
group by uid 
)

select distinct *
from t2
order by act_month_total desc,act_days_2021 desc