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

with 
user_info as 
    (select uid from user_info  where level in (6,7)),
examination_info as 
    (select exam_id from examination_info),
union_table as
    (select uid,start_time as time,exam_id as eq  from exam_record
    union all 
    select uid,submit_time as time,question_id as eq from practice_record)

select
    t1.uid,
    count(distinct date_format(t2.time,'%Y%m')) as act_month_total,
    count(distinct case when year(t2.time) = 2021 then date_format(t2.time,'%m%d') else null end ) as act_days_2021,
    count(distinct case when (year(t2.time) = 2021 and t2.eq in (select exam_id from examination_info)) then date_format(t2.time,'%m%d') else null end) as act_days_2021_exam,
    count(distinct case when (year(t2.time) = 2021 and t2.eq not in (select exam_id from examination_info)) then date_format(t2.time,'%m%d') else null end) as act_days_2021_question
from
    user_info t1
left join
    union_table t2
on 
    t1.uid = t2.uid
group by
    t1.uid
order by
    act_month_total desc,act_days_2021 desc