Postgres 13

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

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    stock_date DATE,
    product VARCHAR,
    stock_balance INT
);

INSERT INTO inventory
(stock_date, product, stock_balance
)
VALUES 
('2025-10-01','prod_01','100'),
('2025-10-01','prod_02','500'),
('2025-10-31','prod_01','800'),
('2025-10-31','prod_02','600'),
('2025-10-31','prod_03','700'),
('2025-10-31','prod_04','400');





CREATE TABLE



INSERT 0 6


select
product as product,
stock_date as stock_date,
sum(stock_balance) as stock_balance
from inventory
group by 1,2
order by 1,2 desc;


product
stock_date
stock_balance


prod_01
2025-10-31
800

prod_01
2025-10-01
100

prod_02
2025-10-31
600

prod_02
2025-10-01
500

prod_03
2025-10-31
700

prod_04
2025-10-31
400



SELECT 6


select
t1.product as product,
t1.stock_date as stock_date,
t1.stock_balance as stock_balance,
LAST_VALUE(t1.stock_balance) OVER (PARTITION BY t1.product ORDER BY stock_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 
FIRST_VALUE(t1.stock_balance) OVER (PARTITION BY t1.product ORDER BY stock_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as change
from
  
  (select
  product as product,
  stock_date as stock_date,
  sum(stock_balance) as stock_balance
  from inventory
  group by 1,2
  order by 1,2 desc) t1

group by 1,2,3
order by 1,2,3;




product
stock_date
stock_balance
change


prod_01
2025-10-01
100
700

prod_01
2025-10-31
800
700

prod_02
2025-10-01
500
100

prod_02
2025-10-31
600
100

prod_03
2025-10-31
700
0

prod_04
2025-10-31
400
0



SELECT 6

product	stock_date	stock_balance
prod_01	2025-10-31	800
prod_01	2025-10-01	100
prod_02	2025-10-31	600
prod_02	2025-10-01	500
prod_03	2025-10-31	700
prod_04	2025-10-31	400