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

select 
    a1.uid,
    a1.act_month_total,
    a2.act_days_2021,
    a3.act_days_2021_exam,
    a4.act_days_2021_question
from (   
-- 总活跃月份数
select
    t1.uid,
    count(distinct substr(t2.time,1,7)) act_month_total	
from (
    select uid from user_info where level = 6 or level = 7
) t1
left join (
    select
        uid,
        start_time `time`
    from exam_record
    union
    select
        uid,
        submit_time `time`
    from practice_record
) t2
on t1.uid = t2.uid
group by uid
) a1
inner join (
-- 2021年活跃天数
select
    t1.uid,
    count(distinct substr(t2.time,1,10)) act_days_2021	
from (
    select uid from user_info where level = 6 or level = 7
) t1
left join (
    select
        uid,
        start_time `time`
    from exam_record where start_time >= '2021-01-01 00:00:00' and start_time <= '2021-12-31 23:59:59'
    union
    select
        uid,
        submit_time `time`
    from practice_record where submit_time >= '2021-01-01 00:00:00' and submit_time <= '2021-12-31 23:59:59'
) t2
on t1.uid = t2.uid
group by uid
) a2
on a1.uid = a2.uid
inner join (
-- 2021年试卷作答活跃天数
select
    t1.uid,
    count(distinct substr(t2.time,1,10)) act_days_2021_exam	
from (
    select uid from user_info where level = 6 or level = 7
) t1
left join (
    select
        uid,
        start_time `time`
    from exam_record where start_time >= '2021-01-01 00:00:00' and start_time <= '2021-12-31 23:59:59'
) t2
on t1.uid = t2.uid
group by uid
) a3
on a1.uid = a3.uid
inner join (
-- 2021年答题活跃天数
select
    t1.uid,
    count(distinct substr(t2.time,1,10)) act_days_2021_question	
from (
    select uid from user_info where level = 6 or level = 7
) t1
left join (
    select
        uid,
        submit_time `time`
    from practice_record where submit_time >= '2021-01-01 00:00:00' and submit_time <= '2021-12-31 23:59:59'
) t2
on t1.uid = t2.uid
group by uid
) a4
on a1.uid = a4.uid
order by act_month_total desc, act_days_2021 desc ;