'Calculating the sum of a column dynamically using lag function on the same un-computed column?

I'm trying to dynamically calculate a running total using the value in the past row:

The logic for computing dynamic_sum in the below table is : rooms + lag(dynamic_sum)

    Day     rooms   dynamic_sum   logic
2021-01-04    1         1        (rooms+lag(dynamic_sum) = 1+0)
2021-01-05    0         1        (rooms+lag(dynamic_sum) = 0+1)
2021-01-06    2         3        (... = 2+1)
2021-01-07   -2         1
2021-01-08    2         3
2021-01-09   -2         1

Select Day, rooms, rooms + lag(dynamic_sum) OVER() AS dynamic_sum from ....

I'm not able to implement this since, dynamic_sum column is getting used in real time.

ERROR:  column "dynamic_sum" does not exist
LINE 1: select Day, rooms, rooms+lag(dynamic_sum...
                                     ^

Any help on how to achieve this kind of result would be greatly appreciated!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source