题解 | #计算用户的平均次日留存率#

SELECT
-- 计算分子
	(SELECT COUNT(*) FROM
	 -- 将原表中的日期向前推一天生成新表,若新表能和原表连接上,则证明该用户在该时间点进行了连续刷题操作,需要注意的是这里的是每个用户可能在同一天进行多次刷题,为避免重复值影响,需要使用DISTINCT
	(SELECT
		DISTINCT q.device_id, q.date
	FROM question_practice_detail q
	INNER JOIN 
	 -- 任何新生成的表都需要给一个别名
		(SELECT DISTINCT device_id, 
		DATE_ADD(date, INTERVAL 1 DAY) AS date
		FROM question_practice_detail) AS q_1
	ON q.device_id = q_1.device_id AND q.date = q_1.date) AS q_2)
/
-- 计算分母,计算每个用户登录天数的去重值
(SELECT(SELECT COUNT(*) FROM
(SELECT DISTINCT device_id, date
FROM question_practice_detail) AS q_3)) AS avg_ret