Postgresql substract two columns and substract remainder from next row

Question

I have a test CTE as follows:

with test_cte (month, company, probability, expected, actual) as (values
('2024-03','CompA',100,4,13),
('2024-03','CompA',80,7,13),
('2024-03','CompA',50,7,13)
)
select * from test_cte

What I would like to do is add a final column called 'expected_revised' which would be 'expected' reduced by the amount in 'actual' (which itself gets depleted row after row).

Edit: additionnally, the decrease in the 'actual' value must cease upon reaching 0, indicating full utilization against the expected value.

The partition should be:

partition by month, company order by probability DESC

The column 'expected-revised' should return:

0 (greatest(0,4-13))
0 (greatest(0,7-(13-4)))
5 (greatest(0,7-(13-4-7)))

Any thoughts? I feel i should use a window function for this but can't figure out how to go about it.

Answer 1

0 (greatest(0,4-13)) => 4 - 13
0 (greatest(0,7-(13-4))) => 7+4 - 13
5 (greatest(0,7-(13-4-7))) => 7+4+7 - 13

You need to substract actual value from the running total of the expected values :

WITH test_cte (month, company, probability, expected, actual) AS (VALUES
    ('2024-03', 'CompA', 100, 4, 13),
    ('2024-03', 'CompA', 80, 7, 13),
    ('2024-03', 'CompA', 50, 7, 13)
)
SELECT *,
        GREATEST(0, SUM(expected) OVER (PARTITION BY month, company ORDER BY probability DESC) - actual) AS expected_revised
FROM test_cte;

Results :

month   company probability expected    actual  expected_revised
2024-03 CompA   100         4           13      0
2024-03 CompA   80          7           13      0
2024-03 CompA   50          7           13      5

Demo here

---

A recent comment introduces a new condition stating that the decrease in the actual value must cease upon reaching 0, indicating full utilization against the expected value.

WITH test_cte (month, company, probability, expected, actual) AS (
  VALUES
    ('2024-03', 'CompA', 100, 4, 10),
    ('2024-03', 'CompA', 80, 7, 10),
    ('2024-03', 'CompA', 50, 7, 10)
),
test_cte2 as (
  SELECT *,
    SUM(expected) OVER (PARTITION BY month, company ORDER BY probability DESC) as running_expected
  FROM test_cte
)
SELECT month, company, probability, expected, actual,
       CASE
         WHEN LAG(running_expected) OVER (PARTITION BY month, company ORDER BY probability DESC) > actual THEN expected
         ELSE GREATEST(0, running_expected - actual)
       END AS expected_revised
FROM test_cte2