SQL Server LEAD function

-- 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
),
-- CONSECUTIVE LOGINS
CTE_CONSEC_PLAYERS AS 
(
    SELECT 
        PLAYER_ID, 
        LEAD(EVENT_DATE,1) OVER (PARTITION BY EVENT_DATE ORDER BY EVENT_DATE) NEXT_DATE 
    FROM 
        ACTIVITY A
    JOIN 
        CTE_FIRST_LOGIN C ON A.PLAYER_ID = C.PLAYER_ID
    WHERE  
        NEXT_DATE = DATEADD(DAY, 1, A.EVENT_DATE) AND C.RN = 1
    GROUP BY 
        A.PLAYER_ID
)
-- FRACTION
SELECT 
    NULLIF(ROUND(1.00 * COUNT(CTE_CONSEC.PLAYER_ID) / COUNT(DISTINCT PLAYER_ID), 2), 0) AS FRACTION 
FROM 
    ACTIVITY 
JOIN 
    CTE_CONSEC_PLAYERS CTE_CONSEC ON CTE_CONSEC.PLAYER_ID = ACTIVITY.PLAYER_ID

I am getting the following error when I run this query.

[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘NEXT_DATE’. (207) (SQLExecDirectW)

This is a leetcode medium question 550. Game Play Analysis IV. I wanted to know why it can’t identify the column NEXT_DATE here and what am I missing? Thanks!

Answer

The problem is in this CTE:

-- CONSECUTIVE LOGINS prep
CTE_CONSEC_PLAYERS AS (
  SELECT 
    PLAYER_ID, 
    LEAD(EVENT_DATE,1) OVER (PARTITION BY EVENT_DATE ORDER BY EVENT_DATE) NEXT_DATE 
  FROM ACTIVITY A
  JOIN CTE_FIRST_LOGIN C  ON A.PLAYER_ID = C.PLAYER_ID
  WHERE  NEXT_DATE = DATEADD(DAY, 1, A.EVENT_DATE) AND C.RN = 1
  GROUP BY A.PLAYER_ID
)

Note that you are creating NEXT_DATE as a column alias in this CTE but also referring to it in the WHERE clause. This is invalid because by SQL clause-ordering rules the NEXT_DATE column alias does not exist until you get to the ORDER BY clause which is the last evaluated clause in a SQL query or subquery. You don’t have an ORDER BY clause in this subquery, so technically the NEXT_DATE column alias only exists to [sub]queries that both come after and reference your CTE_CONSEC_PLAYERS CTE.

To fix this you’d probably want two CTEs like this (untested):

-- CONSECUTIVE LOGINS
CTE_CONSEC_PLAYERS_pre AS (
  SELECT 
    PLAYER_ID, 
    RN,
    EVENT_DATE,
    LEAD(EVENT_DATE,1) OVER (PARTITION BY EVENT_DATE ORDER BY EVENT_DATE) NEXT_DATE 
  FROM ACTIVITY A
  JOIN CTE_FIRST_LOGIN C  ON A.PLAYER_ID = C.PLAYER_ID
)
-- CONSECUTIVE LOGINS
CTE_CONSEC_PLAYERS AS (
  SELECT
    PLAYER_ID, 
    MAX(NEXT_DATE) AS NEXT_DATE,
  FROM CTE_CONSEC_PLAYERS_pre
  WHERE  NEXT_DATE = DATEADD(DAY, 1, EVENT_DATE) AND RN = 1
  GROUP BY PLAYER_ID
)