By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE process_table(day, arrivals, max_output_capacity);
✓
INSERT INTO process_table VALUES
('0', 0, 2),
('1', 2, 3),
('2', 5, 4),
('3', 0, 5),
('4', 0, 5),
('5', 14, 1),
('6', 0, 3);
✓
WITH cte AS
(
SELECT
*,
arrivals - max_output_capacity AS difference
FROM process_table
)
SELECT
day,
arrivals,
max_output_capacity,
MAX
(
difference + LAG(MAX(difference, 0), 1, 0) OVER (ORDER BY day),
0
) AS remaining_next_day
FROM cte;
| day | arrivals | max_output_capacity | remaining_next_day |
|---|---|---|---|
| 0 | 0 | 2 | 0 |
| 1 | 2 | 3 | 0 |
| 2 | 5 | 4 | 1 |
| 3 | 0 | 5 | 0 |
| 4 | 0 | 5 | 0 |
| 5 | 14 | 1 | 13 |
| 6 | 0 | 3 | 10 |