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

SELECT
    -- 计算留存的用户天数 / 总的用户活跃天数
    COUNT(t2.device_id) / COUNT(t1.device_id) AS avg_ret
FROM
    -- t1 代表所有用户活跃的记录 (去重)
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS t1
LEFT JOIN
    -- t2 也代表所有用户活跃的记录，用于匹配第二天是否回归
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS t2
ON
    t1.device_id = t2.device_id  -- 匹配同一个用户
    AND t2.date = DATE_ADD(t1.date, INTERVAL 1 DAY); -- 匹配 t1 的后一天