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

with t1 as (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)
full_record
group by uid),

t2 as (select uid,
count(distinct date(submit_time)) as act_days_2021
from 
(select uid,submit_time from exam_record
where year(submit_time) = 2021
union all 
select uid,submit_time from practice_record
where year(submit_time) = 2021
)
full_record
group by uid),

t3 as (select uid,count(distinct date(submit_time)) as act_days_2021_exam 
from exam_record
where year(submit_time) = 2021
group by uid),

t4 as (select uid,count(distinct date(submit_time)) as act_days_2021_question
from practice_record
where year(submit_time) = 2021
group by uid)

select ui.uid,ifnull(act_month_total,0),ifnull(act_days_2021,0),ifnull(act_days_2021_exam,0),ifnull(act_days_2021_question,0) from
 user_info ui 
left join t1 using(uid)
left join t2 using(uid)
left join t3 using(uid)
left join t4 using(uid)
where ui.level in (6,7)
order by act_month_total desc,act_days_2021 desc