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

select 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(
select uid from user_info
where level in(6,7))as id
left join
(
select uid,ifnull(count(distinct date_format(submit_time,'%Y%m')),0) as act_month_total
from (select uid,submit_time
from exam_record 
union all
select uid,submit_time
from practice_record) as act
where uid in(select uid from user_info
where level in(6,7)
)
group by uid) as actmonth
using(uid)

left join(

select uid,ifnull(count(distinct date_format(submit_time,'%Y%m%d')),0) as act_days_2021
from  (select uid,submit_time
from exam_record 
union all
select uid,submit_time
from practice_record) as act
where uid in(select uid from user_info
where level in(6,7)) and year(submit_time)='2021'
group by uid) as actday
using(uid)
left join
(select uid,count(distinct date_format(submit_time,'%y%m%d'))as act_days_2021_exam	
from exam_record
where year(submit_time)='2021'
group by uid)as examcnt
using(uid)
left join 
(select uid,count(distinct date_format(submit_time,'%y%m%d'))as act_days_2021_question	
from practice_record
where year(submit_time)='2021'
group by uid)as quescnt
using(uid)
order by act_month_total desc ,act_days_2021 desc