小笨蛋看过来| #每个6/7级用户活跃情况#

select 
    u_i.uid as uid,
    # count(NULL)记0，否则没数据不显示
    count(distinct act_month) as act_month_total,                       # 总活跃月份数
    count(distinct case when year(act_time) = 2021                      # 2021年活跃天数
            then act_day else null end) as act_days_2021,
    count(distinct case when year(act_time) = 2021 and tag = 'exam'     # 2021年试卷作答活跃天数
            then act_day else null end) as act_days_2021_exam,   
    count(distinct case when year(act_time) = 2021 and tag = 'question' # 2021年答题活跃天数
            then act_day else null end) as act_days_2021_question
from user_info u_i  
left join        # 一定要将用户表和记录表左连接，不然有些用户没有作答记录的话会被miss掉
    (select 
        uid,
        start_time as act_time,
        date_format(start_time, '%Y%m') as act_month,
        date_format(start_time, '%Y%m%d') as act_day,
        'exam' as tag
    from exam_record
    union all
    select 
        uid,
        submit_time as act_time,
        date_format(submit_time, '%Y%m') as act_month,
        date_format(submit_time, '%Y%m%d') as act_day,
        'question' as tag
      from  practice_record
    ) exam_and_practice  # 答题记录总表
on exam_and_practice.uid = u_i.uid
where u_i.level in (6,7)
group by uid
order by act_month_total desc, act_days_2021 desc

exam_and_practice # 答题记录总表