使用SQL统计过去一年存在30天断档后复购的重激活账户数量

问题背景 #

现有数据表purchase_table，结构如下：

需求：统计过去一年中重新激活的账户数量。规则定义：账户连续30天无购买行为视为失效，需找出曾出现30天购买断档、但后续又产生购买行为的账户。已通过ROW_NUMBER()窗口函数按account_id分区并按purchase_date排序，需完成后续逻辑。

解决方案步骤 #

1. 计算相邻购买记录的间隔天数 #

基于已有的排序逻辑，用LAG()窗口函数获取每个账户上一次购买的日期，再计算两次购买的间隔天数，同时先过滤出过去一年的购买记录：

-- SQL Server版本，其他数据库需调整日期函数
WITH purchase_with_gap AS (
    SELECT
        account_id,
        -- 获取当前账户的上一次购买日期
        LAG(purchase_date) OVER (PARTITION BY account_id ORDER BY purchase_date) AS prev_purchase_date,
        -- 计算两次购买的间隔天数
        DATEDIFF(day, LAG(purchase_date) OVER (PARTITION BY account_id ORDER BY purchase_date), purchase_date) AS days_since_last_purchase
    FROM purchase_table
    -- 过滤过去一年的记录
    WHERE purchase_date >= DATEADD(year, -1, GETDATE())
)

不同数据库的日期函数差异：

• MySQL: 用DATE_SUB(NOW(), INTERVAL 1 YEAR)替代日期过滤条件，DATEDIFF(purchase_date, prev_purchase_date)计算间隔天数
• PostgreSQL: 用NOW() - INTERVAL '1 year'替代日期过滤条件，(purchase_date - prev_purchase_date)::int计算间隔天数

2. 筛选符合重新激活条件的账户 #

对于每个账户，只要存在任意一条记录满足days_since_last_purchase > 30，就说明该账户曾出现30天以上的购买断档，且当前记录就是断档后的购买行为（即完成了重新激活）。

3. 去重统计账户数量 #

对筛选出的账户ID去重后计数，即可得到最终结果。

完整SQL示例（SQL Server版本） #

WITH purchase_with_gap AS (
    SELECT
        account_id,
        DATEDIFF(day, LAG(purchase_date) OVER (PARTITION BY account_id ORDER BY purchase_date), purchase_date) AS days_since_last_purchase
    FROM purchase_table
    WHERE purchase_date >= DATEADD(year, -1, GETDATE())
)
SELECT COUNT(DISTINCT account_id) AS reactivated_account_count
FROM purchase_with_gap
WHERE days_since_last_purchase > 30;

补充说明 #

• 首次购买的记录没有上一次购买日期，days_since_last_purchase会为NULL，这类记录不会被>30的条件匹配，不影响结果。
• 上述逻辑隐含了“账户至少有两次购买记录”的前提，因为只有存在两次及以上购买，才会计算出间隔天数，符合“断档后重新激活”的核心要求。

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