Snowflake中基于双表的客户流失（Churn）分析SQL实现

我有两张表用于客户流失（Churn）分析：

1. 客户订阅数据表（记为subscription_data）：

ACCT    INVOICE_DATE  REVENUE   FIRST_INVOICE   LAST_INVOICE
1234    2021-09-01    10        2021-09-01      2021-12-01
1234    2021-12-01    10        2021-09-01      2021-12-01
5678    2021-06-01    20        2021-06-01      2021-08-01
5678    2021-07-01    20        2021-06-01      2021-08-01
5678    2021-08-01    20        2021-06-01      2021-08-01

2. 全月份表（记为all_months）：包含1970年至今的所有月份，示例如下：

MONTH
1970-01-01
1970-02-01
[ ... ]
2023-02-01
2023-03-01

需要生成的结果表需包含每个客户从FIRST_INVOICE到LAST_INVOICE后一个月的所有月份，缺失订阅记录的月份填充REVENUE为NULL，最终格式如下：

ACCT    INVOICE_DATE  REVENUE   FIRST_INVOICE   LAST_INVOICE
1234    2021-09-01    10        2021-09-01      2021-12-01
1234    2021-10-01    NULL      2021-09-01      2021-12-01
1234    2021-11-01    NULL      2021-09-01      2021-12-01
1234    2021-12-01    10        2021-09-01      2021-12-01
1234    2022-01-01    NULL      2021-09-01      2021-12-01
5678    2021-06-01    20        2021-06-01      2021-08-01
5678    2021-07-01    20        2021-06-01      2021-08-01
5678    2021-08-01    20        2021-06-01      2021-08-01
5678    2021-09-01    NULL      2021-06-01      2021-08-01

Snowflake SQL实现步骤 #

核心思路是先构建每个客户的完整月份序列，再左连接原订阅数据填充收入：

1. 提取客户唯一生命周期信息，避免重复生成序列；
2. 生成客户与月份的笛卡尔积，筛选出目标时间范围的记录；
3. 左连接原订阅数据，自动填充缺失月份的REVENUE为NULL。

完整SQL代码 #

WITH customer_lifecycle AS (
    -- 提取每个客户唯一的生命周期信息
    SELECT DISTINCT
        ACCT,
        FIRST_INVOICE,
        LAST_INVOICE
    FROM subscription_data
),
customer_month_sequence AS (
    -- 生成每个客户的完整月份序列（包含流失当月）
    SELECT
        cl.ACCT,
        am.MONTH AS INVOICE_DATE,
        cl.FIRST_INVOICE,
        cl.LAST_INVOICE
    FROM customer_lifecycle cl
    CROSS JOIN all_months am
    -- 筛选范围：从首次账单月到最后账单月的下一个月
    WHERE am.MONTH BETWEEN cl.FIRST_INVOICE AND DATEADD(MONTH, 1, cl.LAST_INVOICE)
)
-- 左连接原订阅数据，填充收入
SELECT
    cms.ACCT,
    cms.INVOICE_DATE,
    sd.REVENUE,
    cms.FIRST_INVOICE,
    cms.LAST_INVOICE
FROM customer_month_sequence cms
LEFT JOIN subscription_data sd
    ON cms.ACCT = sd.ACCT
    AND cms.INVOICE_DATE = sd.INVOICE_DATE
ORDER BY cms.ACCT, cms.INVOICE_DATE;

代码说明 #

• customer_lifecycle CTE：去重后避免同一客户重复生成月份序列，提升查询效率；
• customer_month_sequence CTE：通过CROSS JOIN覆盖所有客户-月份组合，再用WHERE子句锁定时间范围，确保包含从首次订阅到流失后第一个月的所有节点；
• 最终左连接：保留所有生成的月份记录，匹配到订阅数据则填充REVENUE，未匹配则自动为NULL，精准标记流失月份。

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