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

with base_data as (
    select
        uid,
        date(start_time) as active_dt,
        1 as isexam
    from exam_record
    union all
    select
        uid,
        date(submit_time) as active_dt,
        1 as isexam
    from exam_record 
    union all 
    select
        uid,
        date(submit_time) as active_dt,
        0 as isexam
    from practice_record
)

select
    u.uid,
    count(distinct date_format(b.active_dt,'%Y-%m')) as act_month_total,
    count(distinct if(year(b.active_dt)=2021,b.active_dt,null)) as act_days_2021,
    count(distinct if(year(b.active_dt)=2021 and b.isexam=1,b.active_dt,null)) as act_days_2021_exam,
    count(distinct if(year(b.active_dt)=2021 and b.isexam=0,b.active_dt,null)) as act_days_2021_question
from user_info u
left join base_data b on u.uid = b.uid
where u.level in (6,7)
group by u.uid
order by act_month_total desc, act_days_2021 desc;