Calculate difference between current value and next value without CTE/ inline table

Question

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits.

With Visits AS
(
SELECT [uid],[visit_date]
            ,(SELECT MIN(visit_date) 
             FROM Visit E2 
             WHERE E2.visit_date > E1.visit_date AND 
                   E2.uid= E1.uid ) AS next_visit_date
FROM Visit E1
)
SELECT uid, DATEDIFF(day, visit_date, next_visit_date)
FROM Visits

I know that a CTE or a temp table is not a memory efficient way, so looking to increase efficiency. Thank you

Answer 1

I think efficiency is more reliant on the fact that you are doing 2 scans against the table. You could try using a window function instead:

SELECT uid, visit_date
     , LEAD(visit_date) OVER (PARTITION BY uid 
                              ORDER BY visit_date) as next_visit_date
FROM Visit E1

Now you can calculate the diff from there

SELECT uid, DATEDIFF(day, visit_date, 
                          LEAD(visit_date) OVER (
                              PARTITION BY uid 
                              ORDER BY visit_date
                          ))
FROM Visit E1