How to calculate total balance in sql using sum?

updated question —

I have a table that contains the following columns:

DROP TABLE TABLE_1;
CREATE TABLE TABLE_1(
TRANSACTION_ID number, USER_KEY number,AMOUNT number,CREATED_DATE DATE, UPDATE_DATE DATE
);

insert into TABLE_1
values ('001','1001',75,'2022-12-02','2022-12-03'),
('001','1001',-74.98,'2022-12-02','2022-12-03'),
('001','1001',74.98,'2022-12-03','2022-12-04'),
('001','1001',-75,'2022-12-03','2022-12-04')

I need to calculate the balance based on the update date. In some cases there can be the same update_date for two different records. When I have this, I want to grab the lower value of the balance.

This is the query I have so far:

select * from (
select TRANSACTION_ID,USER_KEY,AMOUNT,CREATED_DATE,UPDATE_DATE,
sum(AMOUNT) over(partition by USER_KEY order by UPDATE_DATE rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TOTAL_BALANCE_AMOUNT
from TABLE_1
) qualify row_number() over (partition by USER_KEY order by UPDATE_DATE DESC, UPDATE_DATE DESC) = 1

In the query above, it’s is grabbing the 75, rather than the 0 after I try to only grab the LAST balance.

Is there a way to include in the qualify query to grab the last balance but if the dates are the same, to grab the lowest balance?

Answer

why is the second query, showing 4 different record balances?

That is the point of “running total”. If the goal is to have a single value per entire window then order by should be skipped:

select USER_KEY, 
       sum(AMOUNT) over(partition by USER_KEY) as TOTAL_BALANCE_AMOUNT
from TABLE1;

The partition by clause could be futher expanded with date to produce output per user_key/date:

select USER_KEY, 
       sum(AMOUNT) over(partition by USER_KEY,date) as TOTAL_BALANCE_AMOUNT
from TABLE1;