Snowflake中基于EVENTS表计算用户生命周期时序的SQL报错问题

• 原语句关联子查询未绑定当前用户ID，会匹配全表的第一条事件而非对应用户的事件，同时存在字段名拼写错误（REGISTRATIONS_TIMESTAMP 实际定义为 REGISTERED_TIMESTAMP），这类非确定性关联子查询是Snowflake明确不支持的语法
• 直接左JOIN不同事件的子集会产生笛卡尔积，导致结果行数膨胀

使用Snowflake支持的LATERAL关联，逐行匹配对应用户符合时间要求的首个事件，逻辑和需求完全对齐：

WITH USER_FIRST_REGISTER AS (
    SELECT 
        USER_ID,
        MIN(DATE_TIME) AS REGISTERED_TIMESTAMP
    FROM EVENTS
    WHERE EVENT_NAME = 'registered'
    GROUP BY USER_ID
)
SELECT
    u.USER_ID,
    u.REGISTERED_TIMESTAMP,
    l.LOG_IN_TIMESTAMP,
    lp.LANDING_PAGE_TIMESTAMP
FROM USER_FIRST_REGISTER u
LEFT JOIN LATERAL (
    SELECT DATE_TIME AS LOG_IN_TIMESTAMP
    FROM EVENTS
    WHERE 
        USER_ID = u.USER_ID
        AND EVENT_NAME = 'log_in'
        AND DATE_TIME >= u.REGISTERED_TIMESTAMP
    ORDER BY DATE_TIME ASC
    LIMIT 1
) l ON TRUE
LEFT JOIN LATERAL (
    SELECT DATE_TIME AS LANDING_PAGE_TIMESTAMP
    FROM EVENTS
    WHERE 
        USER_ID = u.USER_ID
        AND EVENT_NAME = 'landing_page'
        AND DATE_TIME >= l.LOG_IN_TIMESTAMP
    ORDER BY DATE_TIME ASC
    LIMIT 1
) lp ON TRUE;

• 首先用CTE拿到每个用户的首次注册时间，若需要保留无注册事件的用户，去掉WHERE条件改回原CASE WHEN写法即可
• 第一次LATERAL JOIN匹配对应用户注册时间之后的第一个登录事件，无登录事件的用户对应字段返回NULL
• 第二次LATERAL JOIN匹配对应用户登录时间之后的第一个落地页访问事件，无对应事件返回NULL
• 若要提升查询性能，可以给EVENTS表加(USER_ID, EVENT_NAME, DATE_TIME)的联合索引

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