CODEWITHSUNDEEP
sqlpostgresqldatepivotwindow-functions
Stephen
Stephen

Reputation: 67

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

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.

Upvotes: 2

Views: 105

Answers (3)

Amber Williams
Amber Williams

Reputation: 24

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;

Upvotes: 0

GMB
GMB

Reputation: 222482

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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;

Upvotes: 0

Related Questions