题解 | #2021年11月每天新用户的次日留存率#

SELECT 
	DATE(UL.in_time) single_day,
	ROUND(SUM(CASE 
		WHEN EXISTS (
			SELECT 1 
			FROM tb_user_log UL2 
			WHERE UL2.uid = UL.uid 
				AND (DATEDIFF(UL2.in_time, UL.in_time) = 1 
					OR DATEDIFF(UL2.out_time, UL.in_time)) = 1
		) AND NOT EXISTS (
			SELECT 1 
			FROM tb_user_log UL1 
			WHERE UL1.in_time < UL.in_time 
				AND UL1.uid = UL.uid)
		THEN 1
		ELSE 0
		END) /
	SUM(CASE 
		WHEN NOT EXISTS (
			SELECT 1 
			FROM tb_user_log UL1 
			WHERE UL1.in_time < UL.in_time 
				AND UL1.uid = UL.uid)
		THEN 1
		ELSE 0
		END), 2) uv_left_rate
FROM tb_user_log UL 
WHERE EXTRACT(YEAR_MONTH FROM UL.in_time) = '202111'
GROUP BY DATE(UL.in_time)
HAVING uv_left_rate IS NOT NULL
ORDER BY DATE(UL.in_time) ASC;