SQL实现：追踪trigger_send_date后31天内指定状态是否变更

核心逻辑拆成两个必须同时满足的判定规则即可，不需要写太复杂的嵌套逻辑：

• 前置条件：trigger_send_date 触发时点，用户的有效状态必须是04a. Lapsing - Lowering Engagement，也就是触发日当天/之前最近一条状态记录匹配流失状态，直接排除触发时已经不在流失态的无效样本
• 后置条件：从trigger_send_date 次日开始计算，往后31天的时间窗口内，该用户存在至少一条状态为03d. Engaged - Very High的记录
  两个条件同时命中时，你需要的布尔列返回True，否则返回False。

你原有代码的核心问题是窗口函数分区规则写错了：PARTITION BY cust_id, state_date 会把每个用户每个状态日期切成独立分区，完全无法实现跨日期的前后状态追踪。
正确的实现可以参考下面的代码，兼容大部分主流SQL引擎（Spark/Hive/BigQuery等，仅日期函数需要根据你用的引擎微调参数顺序）：

WITH base_state_flag AS (
    SELECT
        cust_id,
        state_date,
        trigger_send_date,
        total_state,
        -- 标记当前记录是否为目标流失态
        total_state = '04a. Lapsing - Lowering Engagement' AS is_lapse,
        -- 标记当前记录是否为目标高活跃态
        total_state = '03d. Engaged - Very High' AS is_very_high_engaged,
        -- 标记当前记录是否落在触发日后31天的观测窗口内
        CASE
            WHEN state_date > trigger_send_date AND DATEDIFF(state_date, trigger_send_date) <= 31 THEN 1
            ELSE 0
        END AS in_post_31d_window
    FROM base
)
SELECT
    cust_id,
    state_date,
    trigger_send_date,
    total_state,
    is_lapse AS lapse,
    -- 同时满足前置+后置条件则返回True
    LAST_VALUE(IF(state_date <= trigger_send_date, is_lapse, NULL) IGNORE NULLS)
        OVER (
            PARTITION BY cust_id
            ORDER BY state_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    AND
    MAX(IF(in_post_31d_window = 1, is_very_high_engaged, FALSE))
        OVER (PARTITION BY cust_id)
    AS lapsed_and_returned_within_31_days
FROM base_state_flag
ORDER BY state_date, trigger_send_date

• 如果你的业务场景里单个用户存在多次trigger_send_date触发记录，需要把trigger_send_date也加入窗口函数的PARTITION BY字段，避免不同批次的触发事件状态互相干扰
• 日期差函数注意适配引擎：Spark/Hive中DATEDIFF(结束日期, 开始日期)返回间隔天数，BigQuery/PostgreSQL中DATEDIFF的日期单位和参数顺序有差异，写之前先做单测验证
• 不需要额外嵌套IF判断返回True/False，SQL中的条件表达式本身就返回布尔值，写法更简洁也不容易出语法错误
• 如果你的SQL引擎不支持IGNORE NULLS语法，可以替换成MAX(IF(..., is_lapse, NULL))的方式取触发前最近的状态值，效果一致。

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