SQL Server LEAD function

Question

-- 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 1

You gave every table an alias (for example JOIN CTE_FIRST_LOGIN C has the alias C), and every column access is via the alias. You need to add the correct alias from the correct table to NEXT_DATE.

Answer 2

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
)

Answer 3

Your primary issue is that NEXT_DATE is a window function, and therefore cannot be referred to in the WHERE because of SQL's order of operations.

But it seems this query is over-complicated.

The problem to be solved appears to be: how many players logged in the day after they first logged in, as a percentage of all players.

This can be done in a single pass (no joins), by using multiple window functions together:

WITH CTE_FIRST_LOGIN AS (
    SELECT
      PLAYER_ID,
      EVENT_DATE,
      ROW_NUMBER() OVER (PARTITION BY PLAYER_ID ORDER BY EVENT_DATE) AS RN,
-- if EVENT_DATE is a datetime and can have multiple per day then group by CAST(EVENT_DATE AS date) first
      LEAD(EVENT_DATE, 1) OVER (PARTITION BY EVENT_DATE ORDER BY EVENT_DATE) AS NextDate
  FROM ACTIVITY
),
BY_PLAYERS AS (
    SELECT
      c.PLAYER_ID,
      SUM(CASE WHEN c.RN = 1 AND c.NextDate = DATEADD(DAY, 1, c.EVENT_DATE)
        THEN 1 END) AS IsConsecutive
    FROM CTE_FIRST_LOGIN AS c
    GROUP BY c.PLAYER_ID
)
SELECT ROUND(
    1.00 *
    COUNT(c.IsConsecutive) /
    NULLIF(COUNT(*), 0)
  ,2) AS FRACTION
FROM BY_PLAYERS AS c;

You could theoretically merge BY_PLAYERS into the outer query and use COUNT(DISTINCT but splitting them feels cleaner