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

with
    temp as (
        select
             a.uid,
             exam_id,
             start_time,
             submit_time,
             score
        from
            (
                select
                    uid,
                    exam_id,
                    start_time,
                    submit_time,
                    score
                from
                    exam_record
                union all
                select
                    uid,
                    question_id exam_id,
                    null as start_time,
                    submit_time,
                    score
                from
                    practice_record
            ) a
            left join user_info b on a.uid = b.uid
        where
            level in (6, 7)
    )
select
    t3.uid,
    ifnull(t1.act_month_total,0) act_month_total,
    ifnull(t2.act_days_2021,0) act_days_2021,
    ifnull(t2.act_days_2021_exam,0) act_days_2021_exam,
    ifnull(t2.act_days_2021_question,0) act_days_2021_question
from
# select * from
    (
    select distinct uid
    from user_info
    where level in(6,7)) t3
    left join 
    (
        select
            uid,
            count(distinct left (submit_time, 7)) act_month_total
        from
            temp
        group by
            uid
    ) t1 on t3.uid = t1.uid
    left join (
        select
            x.uid,
            count(distinct date (submit_time)) act_days_2021,
            count(distinct date (9_sub)) act_days_2021_exam,
            count(distinct date (8_sub)) act_days_2021_question
        from
        (
            select 
                uid,
                submit_time,
                if (left (exam_id, 1) = 9, submit_time, null) as 9_sub,
                if (left (exam_id, 1) = 8, submit_time, null) as 8_sub
            from
                temp
        )x
        where
            year (submit_time) = 2021
        group by
            uid
    ) t2 on t1.uid = t2.uid
order by
    act_month_total desc,
    act_days_2021 desc