SQL留存率计算异常：昨日活跃用户与回流用户数值一致问题排查

你的SQL出现previous_day_active_users和returning_users数值一致的问题，核心原因有两个：

1. 小时级数据未按天聚合，导致LAG逻辑错误：原表是小时粒度，date_trunc('day')后同一用户同一天会有多条记录，LAG(active)取的是该用户上一条小时记录的活跃状态，而非前一天的活跃状态，导致prev_active无法准确标记前一日活跃的用户。
2. JOIN条件缺失日期匹配：注释掉了日期关联条件，导致只要用户在previous_day_active_users中存在（不管日期是否对应）就会被关联，最终两个统计的是同一批用户。

修正后的SQL #

WITH daily_active_users AS (
    -- 先按天聚合，每个用户每天仅保留一条活跃记录
    SELECT
        date_trunc('day', time_eet) AS day_date,
        market_code,
        user_id,
        BOOL SC照(EX理 ​​(AAI�R combinations平calEND有错误，应该是BOOL_OR(active) AS active,
        BOOL_OR(active_casino) AS active_casino,
        BOOL_OR(active_virtual_sports) AS active_virtual_sports,
        BOOL_OR(active_poker) AS active_poker,
        BOOL_OR(active_sportsbook) AS active_sportsbook
    FROM delivery.customer_kpis_hourly
    WHERE time_eet >= '2021-01-01' AND time_eet < '2021-01-05'
    GROUP BY date_trunc('day', time_eet), market_code, user_id
    HAVING BOOL_OR(active) -- 仅保留当日活跃的用户
),
daily_users_with_prev AS (
    SELECT
        day_date,
        market_code,
        user_id,
        active,
        active_casino,
        active_virtual_sports,
        active_poker,
        active_sportsbook,
        -- 按用户+国家分区，取前一天的活跃状态
        LAG(active) OVER (PARTITION BY user_id, market_code ORDER BY day_date) AS prev_day_active
    FROM daily_active_users
),
-- 提取每日活跃用户对应的次日（用于关联当前日的前一日数据）
prev_day_active AS (
    SELECT
        day_date + INTERVAL '1 day' AS current_day,
        market_code,
        user_id
    FROM daily_active_users
)
SELECT
    'D'::text AS time_gran,
    du.day_date AS time_eet,
    du.market_code,
    COUNT(DISTINCT du.user_id) AS active_user,
    COUNT(DISTINCT du.user_id) FILTER (WHERE du.active_casino) AS active_casino,
    COUNT(DISTINCT du.user_id) FILTER (WHERE du.active_virtual_sports) AS active_vs,
    COUNT(DISTINCT du.user_id) FILTER (WHERE du.active_poker) AS active_poker,
    COUNT(DISTINCT du.user_id) FILTER (WHERE du.active_sportsbook) AS active_sb,
    -- 统计当前日对应的前一日活跃用户数
    COUNT(DISTINCT pda.user_id) AS previous_day_active_user,
    -- 统计当日活跃且前一日也活跃的留存用户数
    COUNT(DISTINCT du.user_id) FILTER (WHERE du.prev_day_active) AS returning_users
FROM daily_users_with_prev du
LEFT JOIN prev_day_active pda
    ON du.day_date = pda.current_day
    AND du.market_code = pda.market_code
    AND du.user_id = pda.user_id
GROUP BY du.day_date, du.market_code;

关键修改点 #

• 按天聚合用户数据：用GROUP BY+BOOL_OR()将小时级数据合并为日级，确保每个用户每天只有一条活跃记录，避免LAG取到同一天的小时数据。
• 修正LAG分区逻辑：PARTITION BY user_id, market_code，因为用户在不同国家的活跃是独立的，这样LAG能准确取到该用户前一天在同一国家的活跃状态。
• 完善前一日用户关联：prev_day_active表明确存储每日活跃用户对应的次日，关联时同时匹配日期、国家、用户，确保统计的是当前日对应的前一日活跃用户。
• 简化留存计算：直接用FILTER (WHERE du.prev_day_active)统计留存用户，逻辑更清晰，避免JOIN带来的重复统计问题。

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