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

#1.把满足条件用户的试卷记录和做题记录取并集（筛选用户需要基于user_info分别做左连接），试卷的start_time
#和题目的submit_time统一为actday
with totalinformation as(
select
    uid,b.start_time as actday
from 
    user_info a left join exam_record b using(uid) left join
    practice_record c using(uid)
where    
    level in (6,7)
union
select
    uid,c.submit_time as actday
from 
    user_info a left join exam_record b using(uid) left join
    practice_record c using(uid)
where    
    level in (6,7)
),
#2.把时间精度退化到“年-月”，对用户分组，计数不同的“年-月”即为该用户总共的活跃月数
ans_of_act_month_total as(
select
    uid,
    count(distinct(date_format(actday,'%Y%m'))) as act_month_total
from 
    totalinformation
group by    
    uid
),
#3.把时间精度退化到“年-月-日”，筛选时间为2021年（后3列都限定2021年），计数不同actday，即各用户
#在2021年的活跃天数
ans_of_act_days_2021 as
(
select
    uid,
    count(distinct(date_format(actday,'%Y%m%d'))) as act_days_2021
from 
    totalinformation
where
    actday like '2021%'
group by 
    uid
),
#4.把时间精度退化为年-月-日”，筛选时间为2021年，只计数不同的start_time，即为各个用户2021年试卷活跃天数
ans_of_act_days_2021_exam as(
select
    uid,count(distinct(date_format(start_time,'%Y%m%d'))) as act_days_2021_exam
from 
    user_info a left join exam_record b using(uid)
where    
    level in (6,7) and b.start_time like '2021%'
group by 
    uid
),
#5.与4同理，即为各个用户2021年答题活跃天数
ans_of_act_days_2021_question as(     
select
    uid,count(distinct(date_format(submit_time,'%Y%m%d'))) as act_days_2021_question
from 
    user_info a left join practice_record b using(uid)
where    
    level in (6,7) and b.submit_time like '2021%'
group by 
    uid),
#6.选出6或7级的目标用户
taruser as (
select
    uid
from 
    user_info
where    
    level in (6,7)
)
#7.将2~6的表基于6左连接，对各列的null值取0，得到最终答案
select
    uid,
    if(act_month_total is not null,act_month_total,0) as act_month_total,
    if(act_days_2021 is not null,act_days_2021,0) as act_days_2021,
    if(act_days_2021_exam is not null,act_days_2021_exam,0) as act_days_2021_exam,
    if(act_days_2021_question is not null,act_days_2021_question,0) as act_days_2021_question
from 
    taruser a left join ans_of_act_month_total using(uid) 
    left join ans_of_act_days_2021 using(uid) 
    left join ans_of_act_days_2021_exam using(uid) 
    left join ans_of_act_days_2021_question using(uid) 
order by 
    act_month_total desc,act_days_2021 desc