I have a table with data for each month end and want to know the LEAD and LAG data points either side of each month. For example:
SELECT month_date,
LEAD(month_date) OVER (PARTITION BY id ORDER BY month_date) next_month_date,
LAG(month_date) OVER (PARTITION BY id ORDER BY month_date) previous_month_date
FROM t
I want to know if this is better than:
SELECT month_date,
t2.month_date AS next_month_date,
t3.month_date AS previous_month_date
FROM t
LEFT JOIN t2 ON t.id = t2.id AND t.month_date = ADD_MONTHS(t.month_date,1)
LEFT JOIN t3 ON t.id = t3.id AND t.month_date = ADD_MONTHS(t.month_date,-1)