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

经过一段时间的练习，完成这种题目也没啥问题了，就是效率还比较慢，特别是在优化的时候。如果一下子想不到优雅的解法，应该从简单粗暴的逻辑开始，然后优化逻辑。

select uid,
count(distinct mon)as act_month_total,
count(distinct day)as act_days_2021,
count(distinct if(cnt='e',day,null))as act_days_2021_exam,
count(distinct if(cnt='p',day,null))as act_days_2021_question
from user_info 
left join 
(
select uid,
  date_format(submit_time,'%Y%m')as mon,
  if(year(submit_time)=2021,date_format(submit_time,'%Y%m%d'),null)as day,
  if(year(submit_time)=2021,'p',null) as cnt -- 添加标记
from practice_record
union all 
select uid,
  date_format(submit_time,'%Y%m')as mon,
  if(year(submit_time)=2021,date_format(submit_time,'%Y%m%d'),null)as day,
  if(year(submit_time)=2021,'e',null)as cnt
from exam_record
)c
using(uid)
where level>=6
group by uid
order by act_month_total desc,act_days_2021 desc