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

/*
select t1.uid ,
    count(distinct t2.ym) as act_month_total,
    count(distinct t3.ymd) as act_days_2021,
    count(distinct t4.eymd) as act_days_2021_exam,
    count(distinct t5.pymd) as 	act_days_2021_question
from 
(
    select uid from user_info where level = 6 or level = 7
) as t1
left join
(
select uid, date_format(start_time,'%Y-%m') as ym from exam_record
union 
select uid, date_format(submit_time,"%Y-%m") as ym from practice_record
) as t2
on t1.uid = t2.uid
left join
(
select uid, date(start_time) as ymd from exam_record 
where year(start_time) = 2021
UNION 
select uid, date(submit_time) as ymd from practice_record 
where year(submit_time) = 2021
) as t3
on t1.uid = t3.uid
left join 
(
select uid, date(start_time) as eymd from exam_record 
where year(start_time) = 2021
) as t4
on t1.uid = t4.uid
left join
(
select uid, date(submit_time) as pymd from practice_record 
where year(submit_time) = 2021
) as t5
on t1.uid = t5.uid
group by t1.uid
order by act_month_total desc,act_days_2021 desc;
*/

select ui.uid as uid,
    count(distinct ym) as act_month_total,
    count(distinct case 
        when year(act_time) = 2021 
        then ymd end) as act_days_2021,
    count(distinct case 
        when year(act_time) = 2021 
        and tag = 'exam'
        then ymd end) as act_days_2021_exam,
    count(distinct case 
        when year(act_time) = 2021 
        and tag = 'question'
        then ymd end) as act_days_2021_question
from user_info as ui
left join
(
    select uid,
    start_time as act_time,
    date_format(start_time,"%Y-%m") as ym,
    date(start_time) as ymd,
    'exam' as tag
    from exam_record
    union all
    select uid,
    submit_time as act_time,
    date_format(submit_time,"%Y-%m") as ym,
    date(submit_time) as ymd,
    'question' as tag
    from practice_record  
) as epr
on ui.uid = epr.uid
where ui.level = 6 or ui.level = 7
group by ui.uid
order by act_month_total desc,act_days_2021 desc;