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

select u_i.uid as uid,
       count(distinct act_month) as act_month_total, # 所有的活跃月
       count(distinct case 
             when year(act_time) = 2021   # 分别加限制
             then act_day 
             end) as act_days_2021,
       count(distinct case 
             when year(act_time) = 2021 
             and tag = 'exam' 
             then act_day 
             end) as act_days_2021_exam,
        count(distinct case
             when year(act_time) = 2021
             and tag = 'question'
             then act_day
             end) as act_days_2021_question
from user_info u_i
left join ( 
            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

最开始还想先得到每个6/7级用户的总活跃月数，但是因为涉及到试卷记录表和做题记录表，这是两个不同的字段，必须要先组合UINOIN ALL。

这个代码是copy的，总体思想是先计算出两张记录表的时间和tag数据，再组合，这样两个字段就合并到一起了。再与用户信息表左联结即可，最后加用户的限制条件即可。