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

select 
six_sevent_uid.uid,
count(distinct substring(ep.submit_time,1,7)) as act_month_total,
count(distinct substring(if(year(ep.submit_time)=2021,
ep.submit_time,null),1,10)) as act_days_2021,
count(distinct substring(if(year(ep.submit_time)=2021 and ep.type=1,
ep.submit_time,null),1,10)) as act_days_2021_exam,
count(distinct substring(if(year(ep.submit_time)=2021 and ep.type=2,
ep.submit_time,null),1,10)) as act_days_2021_question

from 
(
select uid
from user_info where level in (6,7)
) as six_sevent_uid
left join 
(
select 
er.uid,
substring(er.submit_time,1,10) as submit_time,
1 as type
from exam_record er
where er.submit_time is not null
union
select 
pr.uid,
substring(pr.submit_time,1,10) as submit_time,
2 as type
from practice_record pr
where pr.submit_time is not null
) as ep on six_sevent_uid.uid=ep.uid
group by six_sevent_uid.uid
order by act_month_total desc,act_days_2021 desc