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

select t1.uid, t1.act_month_total,t1.act_days_2021,t2.act_days_2021_exam,t3.act_days_2021_question
from
#先找到前三列的结果
(
select a.uid,count(distinct left(b.submit_time,7))as act_month_total,count(distinct if(left(b.submit_time,4)='2021',left(b.submit_time,10),null))as act_days_2021
from
(
    select uid
    from user_info
    where level in (6,7)
)a
left join

(
    select uid, start_time as submit_time
    from exam_record
    union all
    select uid, submit_time
    from practice_record
)b
on a.uid=b.uid
group by a.uid
)t1

left join
#再找到试卷与答题活跃天数2021
(
select a.uid,count(distinct if(left(b.submit_time,4)='2021',left(b.submit_time,10),null))as act_days_2021_exam
from
(
    select uid
    from user_info
    where level in (6,7)
)a
left join

(
    select uid, start_time as submit_time
    from exam_record
    # union all
    # select uid, submit_time
    # from practice_record
)b
on a.uid=b.uid
group by a.uid
)t2
on t1.uid=t2.uid

left join
#答题活跃天数
(
select a.uid,count(distinct if(left(b.submit_time,4)='2021',left(b.submit_time,10),null))as act_days_2021_question
from
(
    select uid
    from user_info
    where level in (6,7)
)a
left join

(
    # select uid, start_time as submit_time
    # from exam_record
    # union all
    select uid, submit_time
    from practice_record
)b
on a.uid=b.uid
group by a.uid
)t3
on t1.uid=t3.uid

order by act_month_total desc, act_days_2021 desc