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

select b5.uid,ifnull(act_month_total,0),ifnull(act_days_2021,0)
,ifnull(act_days_2021_exam,0),ifnull(act_days_2021_question,0)
from 
(select uid
from user_info
where level in(6,7)) as b5
left join
###6/7级用户总活跃月份数以及2021年活跃天数
(select uid,count(distinct uid,date_format(date(submit_time),'%Y%m')) as act_month_total,
count(distinct uid,(case when year(submit_time)='2021' 
                    then date(submit_time) end) ) as act_days_2021
from
  (select uid,submit_time
   from exam_record
   union all
   select uid,submit_time
   from practice_record) as b1 
group by uid) as b2
on b5.uid=b2.uid
left join
###2021年试卷作答活跃天数
(select uid,count(distinct uid,date(submit_time)) as act_days_2021_exam
from exam_record
where year(submit_time)='2021'
group by uid) as b3
on b5.uid=b3.uid
left join
###2021年答题活跃天数
(select uid,count(distinct uid,date(submit_time)) as act_days_2021_question
from practice_record
where year(submit_time)='2021'
group by uid) as b4
on b5.uid=b4.uid