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

select i.uid uid,if(m.cnt_m is null,0,m.cnt_m) as act_month_total	,
if(d.cnt_d is null,0,d.cnt_d) as act_days_2021,
if(e.exam_cnt is null,0,e.exam_cnt) as act_days_2021_exam,
if(q.question_cnt is null,0,q.question_cnt) as act_days_2021_question
from user_info i
left join (select uid,count(distinct date(submit_time)) as exam_cnt    #试卷活跃天数
from exam_record where year(submit_time)=2021 group by uid) e on e.uid=i.uid
left join (select uid,count(distinct date(submit_time)) as question_cnt
from practice_record where year(submit_time)=2021  group by uid)q on q.uid=i.uid  #做题活跃天数
left join 
(select t1.uid uid,count(distinct t1.date) cnt_d from     # 总活跃天数
(select distinct uid uid,  date(submit_time) date
from exam_record where year(start_time)=2021
union
select distinct uid uid,  date(submit_time) date
from practice_record where year(submit_time)=2021)t1
group by t1.uid)d on d.uid=i.uid
left join 
(select t2.uid uid,count(distinct t2.date) cnt_m from     #活跃的月份
(select distinct uid uid, date_format(submit_time,'%Y%m') date
from exam_record 
union
select distinct uid uid, date_format(submit_time,'%Y%m') date
from practice_record)t2
group by t2.uid)m on m.uid=i.uid
where i.level in('6','7')
order by 2 desc,3 desc