Facebook用户登录数据中重新激活用户数统计SQL查询的问题修正

首先，咱们来梳理下你原来的方案里存在的几个核心问题：

• 没有对用户的月登录记录去重：同一个用户一个月内多次登录会生成多条重复数据，导致关联和统计出错。
• 忽略了年份维度：只提取月份数字的话，1月的prev_month会变成0，无法匹配上一年12月的记录，这也是你提到的current_month为4时出现错误的原因之一。
• JOIN和CASE判断逻辑有误：子查询没有关联当前用户，会错误地匹配其他用户的上月登录记录，而不是检查当前用户自己上月是否登录。

正确的解决方案（两种写法） #

写法一：使用NOT EXISTS（推荐，逻辑清晰且高效）

这种写法先提取每个用户每个月的登录记录（去重），再通过NOT EXISTS判断用户上月是否没有登录：

WITH user_month_logins AS (
    -- 去重：每个用户每个月只保留一条登录记录
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', login_date) AS login_month
    FROM user_logins
)
SELECT
    -- 格式化输出为YYYY-MM的月份格式
    TO_CHAR(um.login_month, 'YYYY-MM') AS current_month,
    COUNT(DISTINCT um.user_id) AS reactivated_users
FROM user_month_logins um
-- 筛选出：当前用户在上一个月没有登录记录的情况
WHERE NOT EXISTS (
    SELECT 1
    FROM user_month_logins um_prev
    WHERE um_prev.user_id = um.user_id
      AND um_prev.login_month = um.login_month - INTERVAL '1 month'
)
GROUP BY um.login_month
ORDER BY um.login_month;

写法二：使用LEFT JOIN（如果你偏好关联式写法）

通过左关联上月的登录记录，过滤掉上月有登录的用户，剩下的就是重新激活用户：

WITH user_month_logins AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', login_date) AS login_month
    FROM user_logins
)
SELECT
    TO_CHAR(um.login_month, 'YYYY-MM') AS current_month,
    COUNT(DISTINCT um.user_id) AS reactivated_users
FROM user_month_logins um
LEFT JOIN user_month_logins um_prev
    ON um.user_id = um_prev.user_id
    AND um_prev.login_month = um.login_month - INTERVAL '1 month'
-- 左关联后，上月无登录的用户对应的um_prev.user_id会是NULL
WHERE um_prev.user_id IS NULL
GROUP BY um.login_month
ORDER BY um.login_month;

关键优化点说明 #

1. 用DATE_TRUNC处理年月：避免了跨年（比如1月对应上一年12月）的匹配错误，确保月份判断的准确性。
2. 去重处理：同一个用户一个月内多次登录只算一次，避免重复统计和关联错误。
3. 精准的用户匹配：无论是NOT EXISTS还是LEFT JOIN，都严格关联了同一个用户的上月登录记录，确保判断逻辑正确。

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