CODEWITHSUNDEEP
sqlpostgresql
Max
Max

Reputation: 23

Creating a Separate Column for Prior Week Values (PostgreSQL)

How would I go about having a separate column that shows the prior week's value? For example, if Product A's value for 01/03/2021 was 100, I would like 01/10/2021 to show its date value as well as the 01/03/2021 value in a separate column.

Desired table below (for simplicity sake I added random numbers for the prior week values for 01/03 and 01/04):

Date Product Value Prior Week Value
01/03/2021 Product A 100 50
01/04/2021 Product A 200 55
01/10/2021 Product A 600 100
01/11/2021 Product A 700 200
01/03/2021 Product B 250 40
01/04/2021 Product B 550 45
01/10/2021 Product B 460 250
01/11/2021 Product B 100 550

Upvotes: 2

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you want exactly 7 days before, you can use window functions with a range specification:

select t.*,
       max(value) over (partition by product
                        order by date
                        range between '7 day' preceding and '7 day' preceding
                       ) as value_prev_week
from t;

Upvotes: 2

Related Questions