题解 | #每天的日活数及新用户占比#

SELECT 
	dt,
	-- 可能会出现同一天某个用户多次登录的场景,所以需要去重
    COUNT(DISTINCT uid) AS dau,
    ROUND(COUNT(DISTINCT uid_1) / COUNT(DISTINCT uid), 2) AS uv_new_ratio
FROM
-- 第一张子表：统计每天的登记情况
(SELECT uid, DATE(in_time) AS dt
FROM tb_user_log
UNION ALL
SELECT uid, DATE(out_time) AS dt
FROM tb_user_log) AS t1
LEFT JOIN
-- 第二张子表：统计每个用户的初次登录情况
	(SELECT uid AS uid_1, MIN(DATE(in_time)) AS dt_1 
	FROM tb_user_log
	GROUP BY uid) AS t2
ON t1.dt = t2.dt_1 AND t1.uid = t2.uid_1
GROUP BY dt
ORDER BY dt