考虑以下CTE。它们正确地为我提供了每月活动用户的列表。但是，对于非活动用户，它们返回无效的用户ID。对于每个月（例如，2023-09）的“非活动用户”，我指的是在上个月（2023-08）期间活动但在下一个/当前月份（2023-09）不活动的用户。

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')
  )
)

字符串
我需要的最终结果集，包括唯一的用户和他们相应的月份列表。

编辑

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

型