如何正确计算用户次日平均留存率？SQL查询结果异常求助

你的原查询逻辑错误，导致统计结果偏离预期：

• 原语句计算的是有次日登录的用户-日期对数量与总用户-日期对数量的比值，这并非「次日平均留存率」的正确统计方式。
• 次日平均留存率的核心是先计算每日活跃用户中次日仍活跃的比例，再求这些比例的平均值；而原语句的统计方式会忽略不同日期活跃用户数的权重，导致结果偏差。

根据常见的留存率定义，提供两种适配场景的正确查询：

场景1：日活用户次日平均留存率（通用业务定义） #

先统计每日的留存率，再计算所有日期留存率的平均值：

WITH user_login_dates AS (
    -- 去重：每个用户每日仅记一次登录
    SELECT DISTINCT user_id, date::date AS login_date
    FROM login
),
daily_active AS (
    -- 统计每日活跃用户数
    SELECT login_date, COUNT(user_id) AS active_users
    FROM user_login_dates
    GROUP BY login_date
),
daily_retained AS (
    -- 统计每日活跃用户中次日仍登录的用户数
    SELECT
        d1.login_date,
        COUNT(DISTINCT d1.user_id) AS retained_users
    FROM user_login_dates d1
    JOIN user_login_dates d2
        ON d1.user_id = d2.user_id
        AND d2.login_date = d1.login_date + INTERVAL '1 day'
    GROUP BY d1.login_date
)
-- 计算所有有效日期的留存率平均值
SELECT AVG(COALESCE(dr.retained_users::numeric / da.active_users, 0)) AS avg_ret
FROM daily_active da
LEFT JOIN daily_retained dr ON da.login_date = dr.login_date
WHERE da.active_users > 0; -- 跳过无活跃用户的日期

场景2：用户登录日的次日留存率（单用户单日期维度） #

如果需求是统计所有用户登录日中，次日仍登录的比例：

WITH user_login_dates AS (
    SELECT DISTINCT user_id, date::date AS login_date
    FROM login
)
SELECT
    SUM(CASE WHEN EXISTS (
        SELECT 1 FROM user_login_dates d2
        WHERE d2.user_id = d1.user_id
        AND d2.login_date = d1.login_date + INTERVAL '1 day'
    ) THEN 1 ELSE 0 END)::numeric / COUNT(*) AS avg_ret
FROM user_login_dates d1;

内容的提问来源于stack exchange，提问作者Meliodas Dragon