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

SELECT uid,
			count(DISTINCT stm) act_month_total,
			count(DISTINCT if(left(std,4)='2021',std,null)) act_days_2021,
			count(distinct if(left(std,4)='2021' and type='exam',std,null)) act_days_2021_exam,
			count(distinct if(left(std,4)='2021' and type='practice',std,null)) act_days_2021_question					
from 
(		SELECT ui.uid,t1.uid uid2,t1.ans_id,t1.stm,t1.std,t1.type from user_info ui left join (
		SELECT uid,exam_id as ans_id, DATE_FORMAT(submit_time,"%Y%m") as stm,DATE_FORMAT(submit_time,"%Y%m%d") as std,'exam' as type from exam_record
		union all
		SELECT uid,question_id as ans_id,DATE_FORMAT(submit_time,"%Y%m") as stm,DATE_FORMAT(submit_time,"%Y%m%d") as std,'practice' as type from practice_record
		)t1 on ui.uid=t1.uid
where level>=6
)t2
group by uid order by act_month_total desc,act_days_2021 desc;

这题目实在是有难度，不过学到了怎么统计条件为2021年的行数；
解题思路是自己造一个表，因为需要统计总的活跃天数，又需要统计考试和练习的活跃天数，因此表中要有一列能区分这二者。