I have the following test CASE, which works fine and produces the expected results when I use the lag function.
I'm curious, Is there a way this query can be rewritten to use a self JOIN? Any help would be greatly appreciated.
CREATE TABLE sales (yr,total_sale) AS
SELECT 2015, 23000 FROM DUAL UNION ALL
SELECT 2016, 25000 FROM DUAL UNION ALL
SELECT 2017, 34000 FROM DUAL UNION ALL
SELECT 2018, 32000 FROM DUAL UNION ALL
SELECT 2019, 33000 FROM DUAL;
/* works perfectly */
select yr, total_sale,
lag(total_sale) over (order by yr) as previous_total_sale,
total_sale - lag(total_sale) over (order by yr) difference
from sales;