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

#T1:筛选出解题有用的信息,考录效率的话应该在count前将需要的内容清洗提取出来
#T2：用COUNT(IF)筛选出符合题目条件的日期和试卷类型并去重
#T3:和目标用户left join,缺失列用0填充
select t3.uid,
ifnull(act_month_total,0),
ifnull(act_days_2021,0),
ifnull(act_days_2021_exam,0),
ifnull(act_days_2021_question,0)
from
(
select uid
from user_info 
where level=6 or level =7
)t3
left join 
(
select uid,
count(distinct time) 
    as act_month_total,
count(distinct if(year1='2021',md,null)) 
    as act_days_2021,
count(distinct if(year1='2021' and test='exam',md,null))
    as act_days_2021_exam,
count(distinct if(year1='2021' and test='practice',md,null))
    as act_days_2021_question
from 
(
   select uid, date_format(start_time,'%Y%m') as time,
   year(start_time) as year1,
   date_format(start_time,'%m%d') as md,
   'exam' as test
   from exam_record
   where start_time is not null
  union all
   select uid, date_format(submit_time,'%Y%m'),
   year(submit_time) as year1,
   date_format(submit_time,'%m%d') as md,
   'practice' as  test
   from  practice_record
   where submit_time is not null
) t1
group by uid 
) t2 on t3.uid=t2.uid
order by act_month_total desc ,act_days_2021 desc