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

select 
    ui.uid,
    ifnull(act_month_total,0) as act_month_total,
    ifnull(act_days_2021,0) as act_days_2021,
    ifnull(act_days_2021_exam,0) as act_days_2021_exam,
    ifnull(act_days_2021_question,0) as act_days_2021_question
from user_info ui
left join 
(
    select 
    uid,
    count(distinct if(year(active_days)=2021,active_days,null)) as act_days_2021,
    count(distinct date_format(active_days,'%Y-%m')) as act_month_total
    from
    (
        (select er.uid, date_format(er.submit_time,'%Y-%m-%d') as active_days
        from exam_record er where er.submit_time is not null)
        union all
        (select pr.uid, date_format(pr.submit_time,'%Y-%m-%d') as active_days
        from practice_record pr where pr.submit_time is not null) 
    ) as t_merge_record
    group by uid
) mid_t_1
on ui.uid = mid_t_1.uid
left join 
(
    select uid, count(distinct date_format(submit_time,'%Y-%m-%d')) as act_days_2021_exam
    from exam_record er  where er.submit_time is not null  and year(submit_time)=2021  group by er.uid 
) exam_actions
on exam_actions.uid = ui.uid
left join 
(
    select uid, count(distinct date_format(submit_time,'%Y-%m-%d')) as act_days_2021_question
    from practice_record pr  where pr.submit_time is not null and year(submit_time)=2021  group by pr.uid 
) as prictice_actions 
on prictice_actions.uid = ui.uid
where  ui.level in (6,7)
order by act_month_total desc, act_days_2021 desc