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

select
table2.uid
,act_month_total
,act_days_2021
,act_days_2021_exam
,act_days_2021_question
from
(
    select
    uid
    ,count(distinct t1) act_month_total
    ,count(distinct if(year(t2) = 2021,t2,null)) act_days_2021
    from
    (
    select
    ui.uid uid
    ,substring(start_time,1,7) t1
    ,substring(start_time,1,10) t2
    from user_info ui left join exam_record er
    on ui.uid = er.uid
    where level > 5
    union
    select
    ui.uid uid
    ,substring(submit_time,1,7) t1
    ,substring(submit_time,1,10) t2
    from user_info ui left join practice_record pr
    on ui.uid = pr.uid
    where level > 5
    ) table1
    group by 1
) table2
left join
(
    select
    ui.uid uid
    ,count(distinct ui.uid,substring(er.start_time,1,10)) act_days_2021_exam
    ,count(distinct ui.uid,substring(pr.submit_time,1,10)) act_days_2021_question
    from user_info ui left join exam_record er
    on ui.uid = er.uid and  year(start_time) = 2021
    left join practice_record pr
    on ui.uid = pr.uid and year(pr.submit_time) = 2021
    where level >= 6
    group by ui.uid
) table3
on table2.uid = table3.uid
order by 2 desc,3 desc;