SQL Server LEAD函数报错NEXT_DATE列无效（LeetCode 550问题求解）

报错核心原因 #

• SQL执行顺序限制。SQL子句的执行优先级为FROM/JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY，你在CTE_CONSEC_PLAYERS的SELECT中定义的别名NEXT_DATE，在同级的WHERE子句执行时还未生成，因此无法识别，直接报列不存在错误。
• LEAD窗口函数分区逻辑错误。你当前写的是PARTITION BY EVENT_DATE，是按日期分组统计下一条记录的日期，完全不符合「查找同一个玩家下一次登录日期」的需求，应该修改为PARTITION BY PLAYER_ID。
• 隐含计算逻辑错误：最后统计比例时，你对ACTIVITY和CTE_CONSEC_PLAYERS做了内连接，会过滤掉所有没有连续登录的玩家，最终计算出来的比例永远为1，不符合题目要求的「连续登录玩家数/总玩家数」的计算规则。

修正后的代码 #

如果要保留你原有的窗口函数写法，仅修复报错和逻辑问题，调整后代码如下：

-- FIRST LOGIN DATE
WITH CTE_FIRST_LOGIN AS 
(
    SELECT 
        PLAYER_ID, EVENT_DATE, 
        ROW_NUMBER() OVER (PARTITION BY PLAYER_ID ORDER BY EVENT_DATE ASC) AS RN
    FROM ACTIVITY
),
-- 先计算每个玩家的下一次登录日期，再做过滤
CTE_CONSEC_PLAYERS AS 
(
    SELECT DISTINCT PLAYER_ID
    FROM (
        SELECT 
            A.PLAYER_ID, 
            LEAD(EVENT_DATE,1) OVER (PARTITION BY A.PLAYER_ID ORDER BY A.EVENT_DATE) NEXT_DATE,
            C.RN
        FROM ACTIVITY A
        JOIN CTE_FIRST_LOGIN C ON A.PLAYER_ID = C.PLAYER_ID
    ) T
    WHERE NEXT_DATE = DATEADD(DAY, 1, EVENT_DATE) AND RN = 1
)
-- 修正比例计算逻辑
SELECT 
    ROUND(1.0 * COUNT(DISTINCT C.PLAYER_ID) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY), 2) AS FRACTION
FROM ACTIVITY A
LEFT JOIN CTE_CONSEC_PLAYERS C ON A.PLAYER_ID = C.PLAYER_ID

你也可以用更简洁的写法实现题目需求：

-- 先取每个玩家的首次登录日期
WITH CTE_FIRST_LOGIN AS 
(
    SELECT 
        PLAYER_ID, 
        MIN(EVENT_DATE) AS FIRST_LOGIN_DATE
    FROM ACTIVITY
    GROUP BY PLAYER_ID
),
-- 统计符合「首次登录后第二天也登录」条件的玩家
CTE_QUALIFIED_PLAYERS AS
(
    SELECT DISTINCT A.PLAYER_ID
    FROM ACTIVITY A
    JOIN CTE_FIRST_LOGIN F ON A.PLAYER_ID = F.PLAYER_ID
    WHERE A.EVENT_DATE = DATEADD(DAY, 1, F.FIRST_LOGIN_DATE)
)
-- 计算比例
SELECT 
    ROUND(1.0 * COUNT(Q.PLAYER_ID) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY), 2) AS FRACTION
FROM CTE_FIRST_LOGIN F
LEFT JOIN CTE_QUALIFIED_PLAYERS Q ON F.PLAYER_ID = Q.PLAYER_ID

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