题解 | #未完成试卷数大于1的有效用户#

根据 摸鱼学大师 这位大佬的分析思路,感激终于通过了~

```SELECT
	ui.uid AS uid,
	COUNT(DISTINCT act_month) act_month_total,
	-- 6/7级用户总活跃月份数
	COUNT(
		DISTINCT CASE
		WHEN act_time_year = 2021 THEN
			act_day
		END
	) AS act_days_2021,
	-- 2021年活跃天数
	COUNT(
		DISTINCT CASE
		WHEN act_time_year = 2021
		AND tag = 'e' THEN
			act_day
		END
	) AS tagact_days_2021_exam,
	-- 2021年试卷作答活跃天数
	count(
		DISTINCT CASE
		WHEN act_time_year = 2021
		AND tag = 'q' THEN
			act_day
		END
	) AS act_days_2021_question -- 2021年答题活跃天数
FROM
	-- 筛选用户等级6/7的，试卷活跃的用户
	user_info ui
LEFT JOIN (
	SELECT
		uid,
		YEAR (start_time) AS act_time_year,
		-- 将活跃时间直接转化为年，为了统计2021年的活跃数据
		DATE_FORMAT(start_time, '%Y%m') AS act_month,
		-- 活跃时间转化为年月，为了统计活跃年份
		date_format(start_time, '%Y%m%d') AS act_day,
		-- 活跃时间转化为年月日，为了统计活跃天数
		'e' AS tag -- 筛选试卷和练习类型
	FROM
		exam_record
	UNION ALL
		# 全连接
		-- 筛选用户等级6/7的，试卷活跃的用户
		SELECT
			uid,
			YEAR (submit_time) AS act_time_time,
			DATE_FORMAT(submit_time, '%Y%m') AS act_month,
			date_format(submit_time, '%Y%m%d') AS act_day,
			'q' AS tag
		FROM
			practice_record
) AS temp ON ui.uid = temp.uid
WHERE
	ui. LEVEL >= 6 -- 筛选用户等级6/7
GROUP BY
	ui.uid
ORDER BY
	act_month_total DESC,
	act_days_2021 DESC -- 总活跃月份数、2021年活跃天数降序排序