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

select
    uid,
    count(distinct moth) as act_month_total,
    count(
        distinct case
            when year = 2021 then days
        end
    ) as act_days_2021,
    count(
        distinct case
            when year = 2021
            and type = "exam" then days
        end
    ) as act_days_2021,
    count(
        distinct case
            when year = 2021
            and type = "practice" then days
        end
    ) as act_days_2021_exam
from
    (
        select
            uid,
            submit_time,
            date_format (submit_time, "%Y-%m") as moth,
            date_format (submit_time, "%Y-%m-%d") as days,
            date_format (submit_time, "%Y") as year,
            "exam" as type
        from
            exam_record
        union all
        select
            uid,
            submit_time,
            date_format (submit_time, "%Y-%m") as moth,
            date_format (submit_time, "%Y-%m-%d") as days,
            date_format (submit_time, "%Y") as year,
            "practice" as type
        from
            practice_record
    ) t1
    right join user_info using (uid)
where
    level = 7
    or level = 6
group by
    uid
order by
    act_month_total desc,
    act_days_2021 desc