I have a data table that has 3 columns: Week, Type and units. I want to calculate the week over week changes in units for each Type, and the result table will be like this:
Week Type Units Change
2020-12-01 A 10 null
2020-12-01 B 15 null
2020-12-01 C 20 null
2020-12-01 D 18 null
2020-12-08 A 20 10
2020-12-08 B 15 0
2020-12-08 C 25 5
2020-12-08 D 15 -3
...
I tried to use the lag function but failed maybe because I'm confused what to use in partition by and order by. Can someone please help me?
CodePudding user response:
Since you want the previous "Units" for the same "Type", the partition is on the "Type".
SELECT *
, Units - LAG(Units) OVER (PARTITION BY [Type] ORDER BY [Week]) AS Change
FROM data_table