Home > Software design >  How to calculate week over week changes in SQL?
How to calculate week over week changes in SQL?

Time:01-03

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
  • Related