Postgres 14

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

CREATE TABLE t
    (account integer  not null,
     item varchar(1)  not null, 
     units integer  not null, 
     price decimal not null,
     created_at TIMESTAMP(6) WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL)
;
    
INSERT INTO t
    (account,item, units, price, created_at)
VALUES
    (2, 'A', -1, '120.00', '2022-09-23 17:33:07'),
    (2, 'A', 1, '110.00', '2022-09-23 17:34:31'),
    (1, 'B', -1, '70.00', '2022-09-23 17:38:31'),
    (1, 'B', 1, '50.00', '2022-09-23 17:36:31'),
    (1, 'B', 2, '50.00', '2022-09-23 17:40:31'),
    (1, 'B', -1, '60.00', '2022-09-23 17:41:31'),
    (1, 'B', -1, '70.00', '2022-09-23 17:42:31'),
    (1, 'B', 1, '50.00', '2022-09-23 17:35:31'),
    (1, 'B', -1, '60.00', '2022-09-23 17:33:31'),
    (2, 'B', 1, '70.00', '2022-09-23 17:43:31'),
    (2, 'B', 1, '75.00', '2022-09-23 17:45:31'),
    (2, 'B', -2, '80.00', '2022-09-23 17:46:31')
;



CREATE TABLE



INSERT 0 12


select *
from   t


account
item
units
price
created_at


2
A
-1
120.00
2022-09-23 17:33:07+01

2
A
1
110.00
2022-09-23 17:34:31+01

1
B
-1
70.00
2022-09-23 17:38:31+01

1
B
1
50.00
2022-09-23 17:36:31+01

1
B
2
50.00
2022-09-23 17:40:31+01

1
B
-1
60.00
2022-09-23 17:41:31+01

1
B
-1
70.00
2022-09-23 17:42:31+01

1
B
1
50.00
2022-09-23 17:35:31+01

1
B
-1
60.00
2022-09-23 17:33:31+01

2
B
1
70.00
2022-09-23 17:43:31+01

2
B
1
75.00
2022-09-23 17:45:31+01

2
B
-2
80.00
2022-09-23 17:46:31+01



SELECT 12


select  account	
       ,item	
       ,units	
       ,price	
       ,created_at
       ,running_total as open
       ,cost
       ,case running_total when 0 then sum(cost) over(partition by account, item, grp order by created_at) end as net
from   
       (
        select *
              ,count(mark_0) over(partition by account, item order by created_at) as grp
      
        from   (
                select *
                       ,case when lag(running_total) over(partition by account, item order by created_at) = 0 then 1 when lag(running_total) over(partition by account, item order by created_at) is null then 1 end as mark_0
                from   (
                        select *
                               ,sum(units) over(partition by account, item order by created_at) as running_total
                               ,price*units*-1 as cost
                        from   t
                       ) t
               ) t
         ) t
order by created_at


account
item
units
price
created_at
open
cost
net


2
A
-1
120.00
2022-09-23 17:33:07+01
-1
120.00
null

1
B
-1
60.00
2022-09-23 17:33:31+01
-1
60.00
null

2
A
1
110.00
2022-09-23 17:34:31+01
0
-110.00
10.00

1
B
1
50.00
2022-09-23 17:35:31+01
0
-50.00
10.00

1
B
1
50.00
2022-09-23 17:36:31+01
1
-50.00
null

1
B
-1
70.00
2022-09-23 17:38:31+01
0
70.00
20.00

1
B
2
50.00
2022-09-23 17:40:31+01
2
-100.00
null

1
B
-1
60.00
2022-09-23 17:41:31+01
1
60.00
null

1
B
-1
70.00
2022-09-23 17:42:31+01
0
70.00
30.00

2
B
1
70.00
2022-09-23 17:43:31+01
1
-70.00
null

2
B
1
75.00
2022-09-23 17:45:31+01
2
-75.00
null

2
B
-2
80.00
2022-09-23 17:46:31+01
0
160.00
15.00



SELECT 12

account	item	units	price	created_at
2	A	-1	120.00	2022-09-23 17:33:07+01
2	A	1	110.00	2022-09-23 17:34:31+01
1	B	-1	70.00	2022-09-23 17:38:31+01
1	B	1	50.00	2022-09-23 17:36:31+01
1	B	2	50.00	2022-09-23 17:40:31+01
1	B	-1	60.00	2022-09-23 17:41:31+01
1	B	-1	70.00	2022-09-23 17:42:31+01
1	B	1	50.00	2022-09-23 17:35:31+01
1	B	-1	60.00	2022-09-23 17:33:31+01
2	B	1	70.00	2022-09-23 17:43:31+01
2	B	1	75.00	2022-09-23 17:45:31+01
2	B	-2	80.00	2022-09-23 17:46:31+01