Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE price_log (
id int NOT NULL  PRIMARY KEY,
sku text NOT NULL,
date date NOT NULL,
price real NOT NULL,
CONSTRAINT date_sku UNIQUE (sku,date)
)
INSERT INTO price_log VALUES (1,'A','01/01/2022',10);
INSERT INTO price_log VALUES (2,'A','02/01/2022',10);
INSERT INTO price_log VALUES (3,'A','03/01/2022',11);
INSERT INTO price_log VALUES (4,'A','04/01/2022',11);
INSERT INTO price_log VALUES (5,'B','01/01/2022',1);
INSERT INTO price_log VALUES (6,'B','02/01/2022',5.99);
INSERT INTO price_log VALUES (7,'B','03/01/2022',3.5);
INSERT INTO price_log VALUES (8,'B','04/01/2022',12);
INSERT INTO price_log VALUES (9,'C','01/01/2022',2.6);
INSERT INTO price_log VALUES (10,'D','01/01/2022',1.99);
INSERT INTO price_log VALUES (11,'D','02/01/2022',1.99);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

WITH cte AS (
 SELECT sku, price, MAX(date) AS date
 FROM price_log
 GROUP BY sku, price
)
SELECT sku, price, date,
LAG(price,1) OVER (
    PARTITION BY sku
    ORDER BY date
) AS old_price
FROM cte


sku
price
date
old_price


A
10
2022-01-02
null

A
11
2022-01-04
10

B
1
2022-01-01
null

B
5.99
2022-01-02
1

B
3.5
2022-01-03
5.99

B
12
2022-01-04
3.5

C
2.6
2022-01-01
null

D
1.99
2022-01-02
null

sku	price	date	old_price
A	10	2022-01-02	null
A	11	2022-01-04	10
B	1	2022-01-01	null
B	5.99	2022-01-02	1
B	3.5	2022-01-03	5.99
B	12	2022-01-04	3.5
C	2.6	2022-01-01	null
D	1.99	2022-01-02	null