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

select
    uid,
    count(distinct exam_mon) as act_month_total,
    count(
        distinct case
            when year (act_time) = 2021 then exam_day
            else null
        end
    ) as act_days_2021,
    count(
        distinct case
            when year (act_time) = 2021
            and tag = 'exam' then exam_day
            else null
        end
    ) as act_days_2021_exam,
    count(
        distinct case
            when year (act_time) = 2021
            and tag = 'pra' then exam_day
            else null
        end
    ) as act_days_2021_question
from
    user_info ui
    left join (
        select
            uid,
            start_time as act_time,
            date_format (start_time, "%Y%m%d") as exam_day,
            date_format (start_time, "%Y%m") as exam_mon,
            "exam" as tag
        from
            exam_record
        union all
        (
            select
                uid,
                submit_time as act_time,
                date_format (submit_time, "%Y%m%d") as exam_day,
                date_format (submit_time, "%Y%m") as exam_mon,
                "pra" as tag
            from
                practice_record
        )
    ) as exam using (uid)
where
    ui.level >= 6
group by
    uid
order by
    act_month_total desc,
    act_days_2021 desc

看评论区写的，先查出 试卷和联系作答的月份和时间。因为总活跃天数和月份可以用到这些数据，之前想着先查出来在合并，但是那样就复杂了，这个思路很好