从上月活跃用户列表识别非活跃用户的SQL问题排查与修正

问题背景 #

现有CTE可正确生成月度活跃用户列表，但查询非活跃用户（定义为：上月活跃但本月未活跃的用户）时返回无效用户ID，需求是获取包含唯一用户及其对应月份的结果集。

原SQL（存在问题） #

WITH payments AS (
  SELECT "UserId", DATE_TRUNC('month', "PayDate") AS "Month"
  FROM "public"."Payments"
  WHERE "IsPaid" = TRUE
     AND "IsDeleted" = FALSE 
     AND "PayDate" BETWEEN {{START_DATE}} AND {{END_DATE}}
),

active_users AS (
  SELECT "Month", "UserId"
  FROM payments
  GROUP BY "Month", "UserId"
),

inactive_users AS (
  SELECT previous_month."Month" + INTERVAL '1 month' AS "Month", previous_month."UserId"
  FROM active_users AS previous_month
  WHERE NOT EXISTS (
    SELECT 1
    FROM active_users AS current_month
    WHERE previous_month."UserId" = current_month."UserId"
      AND current_month."Month" = (previous_month."Month" + INTERVAL '1 month')
  )
)

修改后的SQL（修正版本） #

WITH payments AS (
  SELECT "UserId", DATE_TRUNC('month', "PayDate") AS "Month"
  FROM "public"."Payments"
  WHERE "IsPaid" = TRUE
     AND "IsDeleted" = FALSE 
     AND "PayDate" BETWEEN '2023-07-01' AND '2023-12-01'
),

active_users AS (
  SELECT DISTINCT "Month", "UserId"
  FROM payments
),

inactive_users AS (
  SELECT a."UserId", a."Month"
  FROM active_users a
  WHERE NOT EXISTS (
    SELECT 1
    FROM payments b
    WHERE a."UserId" = b."UserId" AND (a."Month" + INTERVAL '1 month') = b."Month"
  )
)

SELECT * FROM inactive_users

关键修改点 #

• 将active_users中的GROUP BY替换为DISTINCT，更高效地获取唯一的月度活跃用户记录
• 非活跃用户查询中，将关联表从active_users改为payments，避免因活跃用户数据集范围限制导致的无效ID问题
• 明确指定了日期范围（可按需替换回参数{{START_DATE}}和{{END_DATE}}）
• 新增SELECT * FROM inactive_users语句，直接输出目标结果集

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