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