TSQL - Calculate difference between values from yesterday to today, SELF JOIN question

Question

I have pieced together code from various answers online to get the result I want but, I don't understand why it's working and I would like to know what the JOIN is actually doing where it says RowNum + 1.

The original problem is to calculate the percentage difference between a value from yesterday to today. I'm a little fuzzy on Self Joins, but I do understand self join. When I add the RowNum column, that confuses me.

---

Question

What is T2.RowNum = T1.RowNum + 1 doing in the self join please?

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

CREATE TABLE #T1 (
ProductTotal int
,CountDate date
)

INSERT INTO #t1
VALUES 
       (893911,'20200815')
      ,(888970,'20200816')
      ,(899999,'20200817')
      

WITH cte AS (
SELECT 
 ROW_NUMBER() OVER(ORDER BY CountDate) AS RowNum
,ProductTotal
,CountDate
FROM #t1
WHERE   CountDate > CAST(GETDATE () - 2 AS DATE)
)
SELECT 
    t1.RowNum
   ,t1.ProductTotal   
   ,CAST(((t1.ProductTotal - t2.ProductTotal) * 1.0 / t2.ProductTotal) * 100 AS DECIMAL(10,2))  AS ProductDiff
   ,t1.CountDate
FROM cte AS t1
LEFT JOIN cte t2 ON T2.RowNum = T1.RowNum + 1

Answer 1

Assuming you have values on each day, a better approach uses lag():

SELECT ProductTotal, CountDate,
       (ProductTotal - prev_ProductTotal) * 1.0 / ProductTotal
FROM (SELECT t.*, 
             LAG(ProductTotal) OVER (ORDER BY CountDate) as prev_ProductTotal
      FROM #t1 t
     ) t
WHERE CountDate > CAST(GETDATE () - 1 AS DATE)