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

-- 非常多陷阱的一道题...
-- (1）注意时间：只收集2021年；（2）同一个人当天多次登录算活跃天数为1天；（3）不同人在同一个时间点活动活跃天数算两天
WITH t1 AS
	(SELECT 
		DATE_FORMAT(start_time, '%Y%m') AS month,
		uid,
		COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS cnt
	FROM exam_record 
	WHERE submit_time IS NOT NULL AND DATE_FORMAT(submit_time, '%Y')=2021
	GROUP BY DATE_FORMAT(start_time, '%Y%m'), uid)
SELECT 
	month,
    ROUND(SUM(cnt) / COUNT(DISTINCT uid), 2) AS avg_active_days,
    COUNT(DISTINCT uid) AS mau
FROM t1
GROUP BY month;