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

WITH t1 AS (
    -- 从问卷练习表单中提取不重复的设备 ID 和日期，作为第一天的数据
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
),
t2 AS (
    -- 从问卷练习表单中提取不重复的设备 ID 和日期，作为第二天的数据
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
) 

SELECT coalesce(count(distinct t2.device_id,t2.date)/count(distinct t1.device_id,t1.date),0) AS retention_rate
-- 计算次日留存率，如果分母为 0 则返回 0
FROM t1
-- 以 t1 表为基础进行左连接
LEFT JOIN t2 
    ON t2.date = DATE_ADD(t1.date, INTERVAL 1 DAY) AND t1.device_id = t2.device_id;
-- 连接条件为 t2 的日期是 t1 的日期加一天且设备 ID 相同