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

user_13695032 0
Viewed 77

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 enter image description here

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.

pivot window-functions date postgresql sql
3 Answers

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
user_106767160

Use conditional aggregation:

select user_id,
       (sum(amount) filter (where type = 'credit') -
        coalesce(sum(amount) filter (where type = 'debit'), 0)
       )
from public.transaction t
where t.created_at >= CURRENT_DATE - INTERVAL '30 days' and
      t.created_at < CURRENT_DATE
group by user_id;
user_11440350 
SELECT user_id, 
   c.credit_amount - b.debit_amount AS balance
FROM public.transaction a

JOIN (SELECT 
    user_id, type, amount AS debit_amount, 
  FROM public.transaction 
  where a.type LIKE 'debit') b on a.user_id = b.user_id

JOIN (SELECT
  user_id, type, amount AS credit_amount
  FROM public.transaction 
  where type LIKE 'credit') c on a.user_id = c.user_id

WHERE a.created_at >= CURRENT_DATE - INTERVAL '30 days'
AND a.created_at <= CURRENT_DATE) AS table_1
AND (c.credit_amount - b.debit_amount) > 2000
GROUP BY a.user_id;
user_120270140
Related