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

层层击破

with t as 
(
    select uid,start_time,submit_time,exam_id
    from exam_record
    union all
    select uid,submit_time as start_time,submit_time,question_id as exam_id
    from practice_record
)

select t1.uid,
       case when t2.act_month_total is null then 0 else act_month_total end as act_month_total,
       case when t2.act_days_2021 is null then 0 else act_days_2021 end as act_days_2021,
       case when t2.act_days_2021_exam is null then 0 else act_days_2021_exam end as act_days_2021_exam,
       case when t2.act_days_2021_question is null then 0 else act_days_2021_question end as act_days_2021_question
from 

(    select *
     from
     user_info
     where level= 6 or level=7) 
 t1

left join

(select uid,
       COUNT(distinct DATE_FORMAT(start_time,"%y%m")) as act_month_total,
       count(distinct case when year(start_time)=2021 then date(start_time) else null end) as act_days_2021,
       count(distinct case when year(start_time)=2021 and exam_id in (select exam_id from examination_info) then date(start_time) else null end) as act_days_2021_exam,
       count(distinct case when year(start_time)=2021 and exam_id not in (select exam_id from examination_info) then date(start_time) else null end) as act_days_2021_question
from t
where uid in (select uid from user_info where level=6 or level=7)
group by uid
order by act_month_total DESC,act_days_2021 DESC
) t2
on t1.uid = t2.uid
order by act_month_total DESC,act_days_2021 DESC