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

select uid,
if(act_month_total is null,0,act_month_total),
if(act_days_2021 is null,0,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 
#表1：符合条件的uid
(select uid
from user_info
where level between 6 and 7) as T1

left join 

#表2：用户总活跃月份数  通过纵向连接求得
(select uid,count(distinct date_format(submit_time,'%Y%m')) as act_month_total
from 
(select uid,submit_time
from exam_record
union all   #纵向链接
select uid,submit_time
from practice_record) as t1
group by uid) as T2 using(uid)

left join 
 
#表3：2021年活跃天数  同样通过纵向连接两个record表
(select uid,count(distinct date(submit_time)) AS act_days_2021 # t2
from (select uid,submit_time
from exam_record
union all   #纵向链接
select uid,submit_time
from practice_record) as t2 
where year(submit_time) = 2021
group by uid) as T3 using(uid)

left join 

#表4：2021年试卷作答活跃天数
(select uid,	count(distinct date(submit_time)) as act_days_2021_exam	#t3试卷作答天数
from exam_record
where year(submit_time) = 2021
group by uid) as T4 using(uid)

left join 

#表5：2021年答题活跃天数
(select uid,count(distinct date(submit_time)) as act_days_2021_question #t4答题天数
from practice_record
where year(submit_time) = 2021
group by uid)  as T5 using(uid)

order by act_month_total desc,act_days_2021 desc