我的稀碎答案

（错因分析及待改进的地方）

select uid,count(distinct month0) as act_month_total,
count(distinct if(substring(month0,1,4)=2021,date0,null)) as act_days_2021,
  %%%应用substring筛选太复杂
count(distinct if(substring(month0,1,4)=2021 and 
                  substring(tid,1)=9,date(er.submit_time,null)))
as act_days_2021_exam,
  %%%应用substring筛选太复杂
count(distinct if(substring(month0,1,4)=2021 and 
                  substring(tid,1)=8,date(pr.submit_time,null)))
as act_days_2021_question
%%%应用substring筛选太复杂
from 
(select uid,exam_id as tid
 date_format(submit_time,'%Y%m') as month0,date(submit_time) as date0,
  %%%题意理解有误，作答不提交也算活跃
from exam_record as er
where score is not null 
 
union all
 
select uid,question_id as tid,
 date_format(submit_time,'%Y%m') as month0,
 date(submit_time) as date0
from practice_record as pr
) 
where uid in  %%%这会使得最终表中只存在有作答记录的6,7级用户
(select uid
from user_info
where level=6 or level=7)
group by tid
order by act_month_total desc,act_days_2021 desc

分析：

（此部分的代码块部分仅涉及关键语句，不可直接应用）

筛选对象——6/7级用户

• 筛选方式：left join 而非where uid in，以防筛去无作答记录的6/7级用户
• 筛选语句：left join…on ui.uid=q.uid where level=6 or level=7 (不知道为什么在on里筛选等级不奏效）

筛选变量——总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数

需要注意的点：

（1） 仅后三个变量有2021的限制，所以2021不能在提取数据时就进行筛选，应在变量生成时筛选；

（2） 对于2021年活跃天数，由于存在同一天同时存 在试卷和习题作答记录，要进行合并与去重，相应地，计算后两个变量时要予以区分

• 总活跃月份数——

date_format(submit_time,'%Y%m') as month0
+date_format(submit_time,'%Y%m') as month0
→count(distinct month0) as act_month_total

• 2021年活跃天数——

start_time as act_time
+submit_time as act_time
count(distinct if(year(act_time)=2021,date0,null)) as act_days_2021

• 2021年试卷作答活跃天数——

'exam' as tag
+'question' as tag
count(distinct if(year(act_time)=2021 and tag='exam',date0,null))
as act_days_2021_exam

• 2021年答题活跃天数——

count(distinct if(year(act_time)=2021 and tag='question',date0,null))
as act_days_2021_question

排序方式——按照总活跃月份数、2021年活跃天数降序排序

order by act_month_total desc,act_days_2021 desc

提交答案

select ui.uid,count(distinct month0) as act_month_total,
count(distinct if(year(act_time)=2021,date0,null)) as act_days_2021,
count(distinct if(year(act_time)=2021 and tag='exam',date0,null))
as act_days_2021_exam,
count(distinct if(year(act_time)=2021 and tag='question',date0,null))
as act_days_2021_question
%%%在下表中增加了tag和act_time两个变量，简化了2021年的筛选，对exam和question记录也做了区分
from user_info as ui
left join    %%%保证6、7级以上暂无活跃记录的用户也在表中
(select uid,'exam' as tag,start_time as act_time,
  date_format(start_time,'%Y%m') as month0,date(start_time) as date0
%%%作答不提交也算活跃
from exam_record as er
 
union all
 
select uid,'question' as tag,submit_time as act_time,
 date_format(submit_time,'%Y%m') as month0,
 date(submit_time) as date0
from practice_record as pr
) as q
on ui.uid=q.uid 
where level=6 or level=7    %%%筛选6，7级用户
group by uid
order by act_month_total desc,act_days_2021 desc