There appear to be two key issues here.
One is finding a way to match an 'out' quantity with all relevant 'in' quantities. In your case, row 5 needs to be matched with rows 1 and 3 because it uses both rows' quantities, as follows from the order of ins and outs.
You could try using a method like this. Take the two subsets of the table, ins and outs, and calculate two running totals for each, one including the current value (call it
qty_from) and the other excluding it (i.e. the running total of all preceding values). Let us call the former
qty_to and the latter
qty_from. You will get these results for each subset:
Now join the two subsets using a well-known range matching method:
A.from < B.to AND B.from < A.to. That will give you intersecting ranges – or, translating into our problem, matching ins and outs. For the example given, the resulting set will look like this:
ins.qty_from ins.qty_to outs.qty_from outs.qty_to
------------ ---------- ------------- -----------
0 2000 0 30
0 2000 30 1030
0 2000 1030 2010
2000 3800 1030 2010
You can see that the first 'in' range repeats three times. That is because the first 'in' row lends its quantity to three 'out' rows. You can also see that the last 'out' range is repeated too, meaning it borrows from both 'in' rows.
Now that ins and outs are successfully matched, the big issue is to correctly determine the quantity that an 'out' row borrows from an 'in' row in case it matches more than one 'in'. There may be variations on the logic to use, here is one:
ins.qty_to - outs.qty_from;
- take the current
outs.qty_to - ins.qty_from.
It only remains to apply your formula to the obtained values, which is essentially
SUM(ins.unit_price * borrowed_qty) / outs.qty.
Translating all the above into SQL, we can get a query like this:
ROUND(SUM(x.borrowed_qty * ins.unit_price) / outs.qty, 5) AS unit_price,
COALESCE(SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS qty_to
action = 'in'
) AS ins
COALESCE(SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS qty_to
action = 'out'
) AS outs
ON ins.qty_from < outs.qty_to
AND outs.qty_from < ins.qty_to
CROSS JOIN LATERAL
WHEN ins.qty_to < outs.qty_to THEN
WHEN ins.qty_from > outs.qty_from THEN -ins.qty
ELSE -(ins.qty_to - outs.qty_from)
WHEN outs.qty_from > ins.qty_from THEN outs.qty
ELSE -(outs.qty_to - ins.qty_from)
) AS x (borrowed_qty)
A demo of this solution can be found at Rextester.