How to use alias in where clause in mysql with lead lag functions

Question

I am trying to compare value with an alias but it says scolumn not recognized. Docs says can't use alias in where clause

WITH CTE AS
(
    SELECT
        StockCode AS TopProduct,
        COUNT(CustomerID) AS mostCustomers 
    FROM
        dbfinalweek.`e-commerce`
    GROUP BY 
        StockCode 
    ORDER BY 
        mostCustomers DESC
    LIMIT 1
)
SELECT 
    StockCode AS stock, CustomerID, 
    LEAD(StockCode, 1) OVER (ORDER BY CustomerID, InvoiceDate) AS NextItem,
    LAG(StockCode, 1) OVER () AS PreviousItem,
    InvoiceDate
FROM
    dbfinalweek.`e-commerce` AS table1 
WHERE
    (table1.StockCode = (SELECT CTE.TopProduct FROM CTE)) OR 
    (table1.NextItem = (SELECT CTE.TopProduct FROM CTE))

Here is my query. Any idea how I could make Table1.NextItem work?

question from:https://stackoverflow.com/questions/65864724/how-to-use-alias-in-where-clause-in-mysql-with-lead-lag-functions

Answer 1

Windo function can't be used directly so you must make a second cte for that`

CREATE TABLE `e-commerce` (StockCode int,CustomerID int,mostCustomers int,InvoiceDate date)

with CTE AS
(
    Select 
      StockCode as TopProduct
        ,count(CustomerID) as mostCustomers 
        from `e-commerce`
      group by StockCode 
        order by mostCustomers desc  
        limit 1
),
cte2 as(
select 
     StockCode as stock 
    ,CustomerID
    , Lead(StockCode,1) over( order by CustomerID,InvoiceDate) as NextItem
    ,Lag(StockCode,1) over() as PreviousItem 
    ,InvoiceDate
from `e-commerce` )
SELECT
* FROM cte2
WHERE (stock = (Select CTE.TopProduct from CTE)) OR  (NextItem=(Select CTE.TopProduct from CTE))

stock | CustomerID | NextItem | PreviousItem | InvoiceDate
----: | ---------: | -------: | -----------: | :----------

db<>fiddle here