How to calculate SUM for each criteria in 1 field in SQL?

Question

I am back again lol, I am trying to calculate the following:

find out how many users had a balance above £2000 at least once in the last 30 days, so it should be credit-debit to get each users balance.

I have attached the database

I have tried the following, basically a self join, but the output is missing values.

SELECT user_id, (credit_amount - debit_amount) AS balance
FROM (SELECT A.user_id, A.type, B.type, A.amount AS debit_amount, B.amount AS credit_amount
      FROM public.transaction A, public.transaction B
      WHERE A.user_id = B.user_id
      AND a.type LIKE 'debit'
      AND b.type LIKE 'credit'
      AND A.created_at >= CURRENT_DATE - INTERVAL '30 days'
      AND A.created_at <= CURRENT_DATE) AS table_1
WHERE (credit_amount - debit_amount) > 2000
;

However, user_id 3 is being skipped due to having no credit during the time interval & some values are being missed.. any help would be nice, thank you.

Answer 1

find out how many users had a balance above £2000 at least once in the last 30 days,

You can use window functions to compute the running balance of each user during the period. Then, you just need to count the distinct users whose running balance ever exceeded the threshold:

select count(distinct user_id)  no_users
from (
    select 
        user_id,
        sum(case when type = 'credit' then amount else -amount end) 
            over(partition by user_id order by created_at) balance
    from transaction
    where created_at >= current_date - interval '30' day and created_at < current_date
) t
where balance > 2000