How to calculate the balance on the difference of two amount columns based on date?

user_17346778 0
Viewed 123

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.

enter image description here

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.

enter image description here

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

enter image description here

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

snowflake-cloud-data-platform sql
1 Answers

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:

NUMBER_ID FIRST_AMOUNT SECOND_AMOUNT DATE DIFFERENCE BALANCE
111 -10 0 2021-12-23 -10 0
111 -20 0 2021-12-22 -20 10
111 30 0 2021-12-21 30 30
111 -80 -32 2021-12-20 -48 0
111 48 0 2021-12-19 48 48
111 5 5 2021-12-18 0 0
111 72 72 2021-12-17 0 0
111 150 150 2021-12-16 0 0
user_439920
Related