Sum over partition reset when running total is 0

Question

I have a query that is taking transactions (buying and selling of items) to calculate the gain/loss when the running total resets back to 0.

The fiddle is here: https://www.db-fiddle.com/f/974UVvE6id2rEiBPR78CKx/0

The units of each item can be added and subtracted and each time they come back to 0 for a account and item combination we want to calculate the net result of those transactions.

You can see it working in the fiddle for the first few (when open = 0), however it fails if there are multiple transactions before getting to 0 (eg 1 increment, 2 separate decrements of units).

From this data:

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')
;

I need to produce this result (net is the relevant column which we cannot get right in the fiddle, it shows incorrect values for the last two net values):

account	item	units	price	created_at	open	cost	net
2	A	-1	$120.00	2022-09-23T17:33:07.000Z	-1	$120.00
1	B	-1	$60.00	2022-09-23T17:33:31.000Z	-1	$60.00
2	A	1	$110.00	2022-09-23T17:34:31.000Z	0	-$110.00	$10.00
1	B	1	$50.00	2022-09-23T17:35:31.000Z	0	-$50.00	$10.00
1	B	1	$50.00	2022-09-23T17:36:31.000Z	1	-$50.00
1	B	-1	$70.00	2022-09-23T17:38:31.000Z	0	$70.00	$20.00
1	B	2	$50.00	2022-09-23T17:40:31.000Z	2	-$100.00
1	B	-1	$60.00	2022-09-23T17:41:31.000Z	1	$60.00
1	B	-1	$70.00	2022-09-23T17:42:31.000Z	0	$70.00	$30.00
2	B	1	$70.00	2022-09-23T17:43:31.000Z	1	-$70.00
2	B	1	$75.00	2022-09-23T17:45:31.000Z	2	-$75.00
2	B	-2	$80.00	2022-09-23T17:46:31.000Z	0	$160.00	$15.00

---

View on DB Fiddle

Answer 1

We start by establishing cost and every time the running total is 0. By using lag and count we make groups out of every run that leads to zero divided by account and item. We use the groups we just created and find the running total of cost, but only display the result when our original running_total = 0.

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

Fiddle