By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH
ledger_data (
id, amount, opening_amount, closing_amount
)
AS
(
VALUES
(1, 200, 1000, 1200),
(2, -500, NULL, NULL),
(3, -200, NULL, NULL),
(4, 1000, NULL, NULL)
),
running_total AS
(
SELECT
id,
amount,
SUM(opening_amount)
OVER (ORDER BY id)
AS opening_amount,
SUM(amount)
OVER (ORDER BY id)
AS cumulative_amount
FROM
ledger_data
)
SELECT
id,
amount,
opening_amount + cumulative_amount - amount AS opening_amount,
opening_amount + cumulative_amount AS closing_amount
FROM
running_total
ORDER BY
id
;
| id | amount | opening_amount | closing_amount |
|---|---|---|---|
| 1 | 200 | 1000 | 1200 |
| 2 | -500 | 1200 | 700 |
| 3 | -200 | 700 | 500 |
| 4 | 1000 | 500 | 1500 |
SELECT 4