Writing query in CTE returning the wrong output

Question

I have written a SQL query to find prev year sales one using lag and with nested CTE, and another just without nested CTE. I am seeing difference in output in SQL Server environment.

Without nested CTE (correct output):

WITH cte AS
(
    SELECT * 
    FROM 
        (SELECT
             category, product_id,
             DATEPART(YEAR, order_date) AS order_year,
             SUM(sales) AS sales
         FROM
             namastesql.dbo.orders 
         GROUP BY 
             category, product_id, DATEPART(YEAR, order_date)) a
)
SELECT
    *,
    LAG(sales) OVER (PARTITION BY category ORDER BY order_year) AS prev_year_sales
FROM
    cte  
WHERE
    product_id = 'FUR-FU-10000576'

With nested CTE (wrong output):

with cte as (
    select category, product_id, DATEPART(YEAR, order_date) as order_year, sum(sales) as t_sales
    from namastesql.dbo.orders 
    GROUP BY category, product_id, DATEPART(YEAR, order_date)
),
cte2 as (
    select *, lag(t_sales) over (partition by category order by order_year) as prev_year_sales
    from cte
)  
select * from cte2 where product_id = 'FUR-FU-10000576';

Correct output:

Wrong output from nested CTE:

Any help on this will be appreciated.

Answer 1

product_id was not added in partition by clause which as a result giving unexpected result as it fetch the prev sales from any other products .

Answer 2

A windowed function is processed at the same time as SELECT.

More specifically, this is the order of operations:

• FROM and JOINS
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• SELECT

Notice how SELECT is processed last.

Therefore your queries are not the same. Your first query is filtering before the windowed function. The second query is filtering after.