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

select ui.uid,
ifnull(act_month_total,0) act_month_total,
 ifnull(act_days_2021,0)  act_days_2021,
ifnull(act_days_2021_exam,0)act_days_2021_exam,
 ifnull(act_days_2021_question,0)act_days_2021_question
 from user_info ui
left join 
# 2021年试卷作答活跃天数
(select uid,count(distinct date(start_time)) act_days_2021_exam	
from exam_record where year(start_time)=2021
group by uid)t1 using(uid)
left join 
# 2021年答题活跃天数
(select uid,count(distinct date(submit_time)) act_days_2021_question
from practice_record where year(submit_time)=2021
group by uid)t2 using(uid)
left join 
(select uid,
# 2021年活跃天数
count(if(year(dt)=2021,dt,null)) act_days_2021,
# 活跃月份数
count(distinct date_format(dt,'%Y%m')) act_month_total
from
(select uid,date(start_time) dt
from exam_record 
union 
select uid,date(submit_time) dt
from practice_record)t
group by uid)t3 using(uid)
where level in('6','7')
order by 2 desc,3 desc