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

select e.uid,ifnull(a.act_month_total,0),ifnull(b.act_days_2021,0),ifnull(c.act_days_2021_exam,0),ifnull(d.act_days_2021_question,0)
from
(select uid,count(act_month_total) as act_month_total
from
(select distinct uid,act_month_total
from
(select uid,date_format(start_time,'%Y%m') as act_month_total
from exam_record
union
select uid,date_format(submit_time,'%Y%m') as act_month_total
from practice_record) a) b
group by uid) a
left join
(select uid,count(act_days_2021) as act_days_2021
from
(select distinct uid,act_days_2021
from
(select uid,date_format(start_time,'%Y%m%d') as act_days_2021
from exam_record
union
select uid,date_format(submit_time,'%Y%m%d') as act_days_2021
from practice_record) a) b
where substring(act_days_2021,1,4) = 2021 
group by uid) b
on a.uid = b.uid
left join
(select uid,count(act_days_2021_exam) as act_days_2021_exam
from
(select distinct uid,act_days_2021_exam
from
(select uid,date_format(start_time,'%Y%m%d') as act_days_2021_exam
from exam_record) a
) b
where substring(act_days_2021_exam,1,4) = 2021 
group by uid) c
on a.uid = c.uid
left join
(select uid,count(act_days_2021_question) as act_days_2021_question
from
(select distinct uid,act_days_2021_question
from
(select uid,date_format(submit_time,'%Y%m%d') as act_days_2021_question
from practice_record) a
)b
where substring(act_days_2021_question,1,4) = 2021 
group by uid) d
on a.uid = d.uid
right join user_info e
on a.uid = e.uid
where e.level = 6 or e.level = 7
order by act_month_total desc,act_days_2021 desc;