Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table transactions (
 timestamp int not null, 
 customer varchar(30) not null, 
 item varchar(30) not null, 
 volume int not null
);

create index idx_transactions_timestamp
on transactions (timestamp, item);
insert into transactions 
(timestamp, customer, item, volume) values
  (2000, 'Joe',	'A',	100) 
, (2001, 'Joe',	'A',	200) 
, (2001, 'Doe',	'A',	100) 
3 rows affected

create table valuations (
 timestamp int not null, 
 item varchar(30) not null, 
 price decimal(10,1) not null
);

create index idx_valuations_timestamp
on valuations (timestamp, item);
insert into valuations
(timestamp, item, price) values
  (2000, 'A', 1.1) 
, (2001, 'A', 1.2) 
, (2002, 'A', 1.3) 
3 rows affected

create temporary table tmp_customer_valuations (
 timestamp int not null, 
 item varchar(30) not null, 
 customer varchar(30) not null, 
 price decimal(10,1) not null
);
insert into tmp_customer_valuations
(timestamp, item, price, customer)
select v.timestamp, v.item, v.price, c.customer
from valuations v
join (
  select item, customer, min(timestamp) as min_timestamp
  from transactions
  group by item, customer 
) c
  on c.item = v.item
 and c.min_timestamp <= v.timestamp
5 rows affected

create index idx_tmp_customer_valuations
on tmp_customer_valuations (timestamp, item)
select 
  tmp.timestamp
, tmp.customer
, tmp.item
--, tr.volume as order_volume,
, sum(coalesce(tr.volume, 0)) 
     over (partition by tmp.item, tmp.customer 
           order by tmp.timestamp) as stock
, tmp.price * sum(coalesce(tr.volume, 0)) 
     over (partition by tmp.item, tmp.customer 
           order by tmp.timestamp) as value
from tmp_customer_valuations tmp
left join transactions tr
  on tr.timestamp = tmp.timestamp 
 and tr.item = tmp.item
 and tr.customer = tmp.customer
order by
 tmp.customer desc,
 tmp.item,
 tmp.timestamp;


timestamp
customer
item
stock
value


2000
Joe
A
100
110.0

2001
Joe
A
300
360.0

2002
Joe
A
300
390.0

2001
Doe
A
100
120.0

2002
Doe
A
100
130.0
timestamp	customer	item	stock	value
2000	Joe	A	100	110.0
2001	Joe	A	300	360.0
2002	Joe	A	300	390.0
2001	Doe	A	100	120.0
2002	Doe	A	100	130.0