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

select
    ui.uid,
    count(distinct date_format (start_time, '%Y_%m')) as act_month_total,
    count(
        distinct if (
            year (start_time) = 2021,
            date_format (start_time, '%Y%m%d'),
            null
        )
    ) as act_days_2021,
    count(
        distinct if (
            year (start_time) = 2021
            and tag = 'exam',
            date_format (start_time, '%Y%m%d'),
            null
        )
    ) as act_days_2021_exam,
    count(
        distinct if (
            year (start_time) = 2021
            and tag = 'test',
            date_format (start_time, '%Y_%m_%d'),
            null
        )
    ) as act_days_2021_question
from
    (
        select
            uid,
            exam_id as exam_id,
            start_time as start_time,
            'exam' as tag
        from
            exam_record
        union all
        select
            uid,
            question_id as exam_id,
            submit_time as start_time, 'test' as tag
        from
            practice_record
    ) as total
right join user_info as ui
on ui.uid = total.uid
where ui.level >= 6
group by
    ui.uid
order by act_month_total desc, act_days_2021 desc