题解 | #平均活跃天数和月活人数#

-- ①筛选答题时间在2021年的活跃答题记录数
	SELECT
	  *
  FROM
	  exam_record
  WHERE
	  YEAR(submit_time) = 2021
  ORDER BY
	  submit_time;

-- ②根据题意需要统计每个月答题情况，统计出用户平均月活跃数 avg_active_days，及月度活跃人数mau
-- 其中用户平均月活跃数 = 月答题天数 （同一用户一天可能答题并提交多次，仅算一次）/ 当月活跃用户数 (同一用户需要去重)，月答题天数：COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d')
-- 月度活跃人数mau = 当月活跃用户数(同一用户需要去重) 即：COUNT(DISTINCT uid)

SELECT
	 DATE_FORMAT(submit_time, '%Y%m') month,
	 ROUND(COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d')) / COUNT(DISTINCT uid),2) AS avg_active_days,
	 COUNT(DISTINCT uid) AS mau
FROM
	exam_record
WHERE
	YEAR(submit_time) = 2021
GROUP BY
	DATE_FORMAT(submit_time, '%Y%m');