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

select uid,act_month_total,if(act_days_2021 is null,0,act_days_2021) act_days_2021,if(act_days_2021_exam is null,0,act_days_2021_exam),if(act_days_2021_question is null,0,act_days_2021_question)
from (
select uid,sum(if(da is not null,1,0)) act_month_total
from (
select uid,date_format(submit_time,'%Y-%m') as da
from user_info a left join practice_record b
using(uid) 
where level	>=6
group by uid,date_format(submit_time,'%Y-%m')
union 
select uid,date_format(start_time,'%Y-%m') as da
from user_info a left join exam_record b
using(uid) 
where level	>=6
group by uid,date_format(start_time,'%Y-%m') 
) pr1
group by uid
) ep1 left join (select uid,count(1) as act_days_2021
from (
select uid,date_format(submit_time,'%Y-%m-%d') as cn
from user_info a left join practice_record b
using(uid) 
where level	>=6 and date_format(submit_time,'%Y')=2021
group by uid,date_format(submit_time,'%Y-%m-%d')
union 
select uid,date_format(start_time,'%Y-%m-%d') as cn
from user_info a left join exam_record b
using(uid) 
where level	>=6 and date_format(start_time,'%Y')=2021
group by uid,date_format(start_time,'%Y-%m-%d')
) zp
group by uid) ep2
using (uid) left join (select uid,sum(e1) act_days_2021_exam,sum(e2) act_days_2021_question
from (
SELECT UID,0 as e1,count(1) as e2
FROM(
select uid,date_format(submit_time,'%Y-%m-%d') as cn2
from user_info a left join practice_record b
using(uid) 
where level	>=6 and date_format(submit_time,'%Y')=2021
group by uid,date_format(submit_time,'%Y-%m-%d')
) pr1
group by uid
union all
SELECT UID,count(1) as e1,0 as e2
FROM (
select uid,date_format(start_time,'%Y-%m-%d') as cn1
from user_info a left join exam_record b
using(uid) 
where level	>=6 and date_format(start_time,'%Y')=2021
group by uid,date_format(start_time,'%Y-%m-%d')
) ex1
group by uid
) zong
group by uid) ep3
using(uid)
order by act_month_total desc ,act_days_2021 desc