0
445
VisualizaçõesHow 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 
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.
3 Respostas
Responde à pergunta0
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
0
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;
0
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;