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

/*
 统计每个6/7级用户
 总活跃月份数、 act_month_total
 2021年活跃天数、 act_days_2021
 2021年试卷作答活跃天数 act_days_2021_exam
 2021年答题活跃天数 act_days_2021_question
 
 按照总活跃月份数、2021年活跃天数降序排序
 */
select
    uid,
    count(distinct date_format(submit_time, '%Y%m')) as act_month_total,
    count(distinct if(year(submit_time) = '2021',date_format(submit_time, '%Y%m%d'),null)) as act_days_2021,
    count(distinct if(year(submit_time) = '2021' && flag = 'exam',date_format(submit_time, '%Y%m%d'),null)) as act_days_2021_exam,
    count(distinct if(year(submit_time) = '2021' && flag = 'practice',date_format(submit_time, '%Y%m%d'),null)) as act_days_2021_question
from
    (
        select
            uid,
            start_time as submit_time,
            'exam' as flag
        from
            exam_record
        union all
        select
            uid,
            submit_time as submit_time,
            'practice' as flag
        from
            practice_record
    ) t1
    right join user_info using (uid)
where level in (6, 7)
group by uid
order by act_month_total desc,act_days_2021 desc;