Calculate sales depending on stock entries

I'm working on a BI request and I'm stuck and need your help :-)

Here's the context : My company is receiving products and selling them. Until here, nothing complex :-) The selling price is varying depending on my available stock and future incoming products. The operation to fix the selling price is manual. We register the historic of the price in a table.

I would like to know for each income, the total selling price. Some incomes can overlaps each other.

The stock rule is FIFO. So we first complete the first income with the firsts sold quantities the the oder one and so on. At the time of the request, it's possible that we still have stock in our warehouse.

Here's an example :

So the result should be :

Income 1 : 10300  -> 20 *100 + 40 *80 + 50 *100 + 10 (to complete 120 qty) *90 = 10300
Income 2 : 9100   -> 30 (the rest of sale on 01/06/24 : 40-10) *90 + 20 *100 + 40 *90 + 10 (to complete 100 qty) *80 = 9100
Income 3 : 2400   -> 30 (the rest of sale on 01/10/24 : 40-10) *80 = 2400.

There is still 30 in stock but don't need to appear in the result.

I'm working in POSTGRE SQL

An interesting problem! I will use the word "sale" for negative amounts, and for "income" I use "supply" (positive amounts).

One possible query plan is to number each stock item on the order it is supplied, and then draw items for sale in the order of stock number. That is, the first supply here generates items 1-120, the second supply generates items 121-220. On the sales side, the first sale will consume items 1-20, and second items 21-60 and so on.

We can then use these stock numbers like a time line to relate sales with supplies, that is combine sales with supplies by overlaping stock numbers.

Here is a db<>fiddle. I will explain the steps below.

The first step provides the input data which you provided with the question:

with
data(ts, amount, price) as (
  values ('01/01/24', 120, null),
         ('01/02/24', -20,  100),
         ('01/03/24', -40,   80),
         ('01/04/24', 100, null),
         ('01/05/24', -50,  100),
         ('01/06/24', -40,   90),
         ('01/07/24',  60, null),
         ('01/08/24', -20,  100),
         ('01/09/24', -40,   90),
         ('01/10/24', -40,   80)
),

Next, we sum the amounts, ordered by timestamps, to provide the end-points of stock item number intervals. We do this separately for sales and supplies, and we sum the absolute value for sales, of course:

sums as (
  select ts, amount, sum(abs(amount)) over (partition by sign(amount) order by ts), price
  from    data
),

Next, we build the intervals from the interval endpoints, by the calculating the start-points. We no longer need the amount, but we need to know if it was a sale or supply:

intervals as (
  select ts, amount < 0 sale, int8range(sum - abs(amount), sum) r, price
  from sums
),

We want to join the supplies with sales on overlapping stock item number intervals. For this we need to split the transactions into supplies and sales:

sale as (
  select ts, r, price
  from intervals
  where sale
),
supply as (
  select ts, r
  from intervals
  where not sale
),

The next step is to join them. We use the range overlap (&&) and intersection (*) operators:

partial_sales as (
  select supply.ts supply, sale.ts sale, upper(supply.r * sale.r)-lower(supply.r * sale.r) amount, price
  from   supply, sale
  where  supply.r && sale.r

The result at this stage is

It is easy to see that the supply from 01/01/24 combines with four sales, and the sales at 01/06/24 matches two supplies, each contributing some to the sale.

All that remains here is to group by the supply date, and calculate the average profit per supply:

select supply, sum(amount*price)/sum(amount) "avg sales price"
from partial_sales
group by supply
order by supply

The result is:

There are other approaches to this, like continuing accumulation, e.g. ValNiks solution above. It is convenient to use ranges, but it also works without. The best approach depends on circumstances and performance or other non-functional requirements.