I have a table that has two different columns for AMOUNTS. FIRST_AMOUNT & SECOND_AMOUNT. I need to find the balance of the difference of the two.

For example, the table below for each number_id, we have two separate amount columns. I need to take the (FIRST_AMOUNT - SECOND_AMOUNT) to calculate the difference. Then based on this, calculate the BALANCE in the query.

However, because of the way we are receiving the data, we need to subtract the ABSOLUTE values of the amounts. So ABS(FIRST_AMOUNT) - ABS(SECOND_AMOUNT). However, if both the FIRST_AMOUNT and SECOND_AMOUNT have negative, we need the subtraction to also include a negative. So -80 (first_amount) and -32 (second_amount) would be (80-32) WITH a negative, so -48. If there is a negative on only one of the columns, take normal subtraction. So -10 (first_amount) and 0 (second_amount) would be -10.

Then I need a column that calculates the balance of the difference starting from the EARLIEST date.

However, in the final query, I would not like to display the difference column, only the balance. Something like this:

Is there a way to formulate a query that could give this result?

CodePudding user response：

In Snowflake this is trivial, as you can reference to prior output calculations in the following, and Snowflake can see how to resolve the order of dependencies, you can just say what you want.

This break down when you start downing nested window functions, but for now, it can be written in one block.

WITH data(number_id, first_amount, second_amount, date) AS (
    SELECT column1, column2, column3, to_date(column4, 'YYYY-MM-DD') FROM VALUES
    (111, -10, 0, '2021-12-23'),
    (111, -20, 0, '2021-12-22'),
    (111, 30, 0, '2021-12-21'),
    (111, -80, -32, '2021-12-20'),
    (111, 48, 0, '2021-12-19'),
    (111, 5, 5, '2021-12-18'),
    (111, 72, 72, '2021-12-17'),
    (111, 150, 150, '2021-12-16')
)
SELECT number_id
    ,FIRST_AMOUNT 
    ,SECOND_AMOUNT
    ,date
    ,FIRST_AMOUNT - SECOND_AMOUNT as difference
    ,sum( difference )over(partition by number_id order by date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as balance
FROM data
ORDER BY 1, 4 DESC;

gives: