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

#把6、7级用户的exam_record和practice_record纵向组合在一起，新增一列tp标记试卷和题目
WITH temp AS(
	SELECT ui.uid,'exam' tp,DATE_FORMAT(er.`start_time`,'%Y%m') mon,DATE(er.`start_time`) da
	FROM user_info  ui LEFT JOIN exam_record er 
	ON ui.`uid`=er.`uid`
	WHERE ui.`level`=6 OR ui.`level`=7
	UNION ALL
	SELECT ui.uid,'practice' tp,DATE_FORMAT(pr.submit_time,'%Y%m') mon,DATE(pr.submit_time) da
	FROM user_info  ui LEFT JOIN practice_record pr 
	ON ui.`uid`=pr.`uid`
	WHERE ui.`level`=6 OR ui.`level`=7
	),
	#求6/7级用户总活跃月份数（包括所有年份）
	a AS(
		SELECT uid,COUNT(DISTINCT mon) act_month_total
		FROM temp
		GROUP BY uid
	),
	#求6/7级用户2021年试卷、题目活跃天数，这时标签列tp起了很大作用
	#注意年份筛选不要放WHERE里，会把没有活跃记录的用户误删了
	#比如1003是6/7级用户，在2021年没有活跃记录
	b AS(
		SELECT uid,COUNT(DISTINCT IF(YEAR(da)='2021',da,NULL)) act_days_2021,
		COUNT(DISTINCT IF(tp='exam' AND YEAR(da)='2021',da,NULL)) act_days_2021_examact,
		COUNT(DISTINCT IF(tp='practice' AND YEAR(da)='2021',da,NULL)) act_days_2021_question
		FROM temp
		GROUP BY uid
	)
SELECT a.uid,act_month_total,act_days_2021,act_days_2021_examact,act_days_2021_question
FROM a JOIN b ON a.uid=b.uid
ORDER BY act_month_total DESC,act_days_2021 DESC