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

#user_info uid
#examination_info exam_id
#exam_record uid+exam_id+start_time

#practice_record uid+question_id+submit_time

select
t.uid -- 全部用户
,count(distinct a.dt) as act_month_total -- 总活跃月份数
,count(distinct b.dt) as act_days_2021  -- 2021年活跃天数
,count(distinct c.exam_dt) as act_days_2021_exam -- 2021年试卷作答活跃天数
,count(distinct d.submit_dt) as act_days_2021_question -- 2021年答题活跃天数
from
    (select
    uid
    ,left(start_time,7) as dt
    from exam_record
    union all
    select
    uid
    ,left(submit_time,7) as dt
    from practice_record) a -- 不限制表
left join
    (select
    uid
    ,date(start_time) as dt
    from exam_record
    where left(start_time,4)=2021
    union all
    select
    uid
    ,date(submit_time) as dt
    from practice_record
    where left(submit_time,4)=2021) b -- 时间限制表
on a.uid = b.uid
left join
    (select
    uid
    ,date(start_time) as exam_dt
    from exam_record
    where left(start_time,4)=2021) c -- 时间限制作答表
on a.uid=c.uid
left join
    (select
    uid
    ,date(submit_time) as submit_dt
    from practice_record
    where left(submit_time,4)=2021) d -- 时间限制练习表
on a.uid=d.uid
right join user_info t
on a.uid= t.uid
where level>=6
group by 1
order by act_month_total desc,act_days_2021 desc;