Calculate the Running Total logic but first cell is coming up blank

Question

I need to create a running total for a report. I have tried using unbounded window function but my first cell is coming up blank. I need the same logic in the first cell too. Below is the initial amount I have in the table. I am using it to create the running amount as per reference number in descending order

Initial Amount = 995

Logic tried:

InitialAmount - SUM (Amount)  OVER(PARTITION BY ID Order By REFERENCE_NUMBER DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS RUNNING_AMOUNT

Result:

Expected Result:

Answer 1

The problem is that your window is empty for the first row, so the sum() yields null, which propagates to the result of the substraction.

You can work around this with coalesce():

InitialAmount 
    - COALESCE(
        SUM (Amount) OVER(
            PARTITION BY ID 
            Order By REFERENCE_NUMBER DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS RUNNING_AMOUNT