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

select
    c.uid,
    if (act_month_total is null, 0, act_month_total) act_month_total,
    if (act_days_2021 is null, 0, act_days_2021) act_days_2021,
    if (act_days_2021_exam is null, 0, act_days_2021_exam) act_days_2021_exam,
    if (
        act_days_2021_question is null,
        0,
        act_days_2021_question
    ) act_days_2021_question
from
    (
        select
            uid
        from
            test.user_info
        where
            level between 6 and 7
    ) c
    left join (
        select
            a.uid,
            count(distinct date_Format (time, '%Y%m')) act_month_total
        from
            (
                select
                    uid,
                    exam_id tid,
                    start_time time,
                    score
                from
                    test.exam_record
                union all
                select
                    uid,
                    question_id tid,
                    submit_time time,
                    score
                from
                    test.practice_record
            ) a
            join test.user_info ui on a.uid = ui.uid
        where
            level between 6 and 7
        group by
            a.uid
    ) d on c.uid = d.uid
    left join (
        select
            a.uid,
            count(distinct date_Format (time, '%Y%m%d')) act_days_2021
        from
            (
                select
                    uid,
                    exam_id tid,
                    start_time time,
                    score
                from
                    test.exam_record
                union all
                select
                    uid,
                    question_id tid,
                    submit_time time,
                    score
                from
                    test.practice_record
            ) a
            join test.user_info ui on a.uid = ui.uid
        where
            level between 6 and 7
            and year (time) = 2021
        group by
            a.uid
    ) e on c.uid = e.uid
    left join (
        select
            ui.uid,
            count(distinct date_Format (start_time, '%Y%m%d')) act_days_2021_exam
        from
            test.user_info ui
            join test.exam_record er on ui.uid = er.uid
        where
            level between 6 and 7
            and year (start_time) = 2021
        group by
            uid
    ) f on c.uid = f.uid
    left join (
        select
            ui.uid,
            count(distinct date_Format (submit_time, '%Y%m%d')) act_days_2021_question
        from
            test.user_info ui
            join test.practice_record pr on ui.uid = pr.uid
        where
            level between 6 and 7
            and year (submit_time) = 2021
        group by
            uid
    ) g on c.uid = g.uid
order by
    act_month_total desc,
    act_days_2021 desc