How to get Total Overdraft amounts from a particular Date in SQL

I’m trying to get the total amount of overdraft accounts from an old Date, the goal is to get the total amount it was on the 31st of January.

I have the following tables Users and Transactions.

USERS (currently)

| user_id |  name   | account_balance  |
|---------|---------|------------------|
|     1   |  Wells  |    1.00          |
|     2   |  John   |    -10.00        |
|     3   |  Sahar  |    -5.00         |
|     4   |  Peter  |    1.00          |

TRANSACTIONS (daily transition can go back in time)

|  trans_id  | user_id |  amount_tendered  |   trans_datetime    |
|------------|---------|-------------------|---------------------|
|    1       |    1    |       2           |     2021-02-16      |
|    2       |    2    |       3           |     2021-02-16      |
|    3       |    3    |       5           |     2021-02-16      |
|    4       |    4    |       2           |     2021-02-16      |
|    5       |    1    |       10          |     2021-02-15      |

so the current total overdraft amount is

SELECT sum(account_balance) AS O_D_Amount 
FROM users 
WHERE account_balance < 0;

| O_D_Amount |
|------------|
|  -15       |

I need Help to reverse this amount to a date in history.

Answer

Assuming overdrafts are based on the sum of transactions up to a point, you can use a subquery:

select sum(total) as total_overdraft
from (select user_id, sum(amount_tendered) as total
      from transactions t
      where t.trans_datetime <= ?
      group by user_id
     ) t
where total < 0;

The ? is a parameter placeholder for the date/time you care about.

Advertisement

Answer