Users table:
| user_id | user_name | credit |
| ------- | --------- | ------ |
| 1 | Winston | 100 |
| 2 | Moustafa | 600 |
| 3 | Jonathan | 800 |
| 4 | Maria | 100 |
Transactions table:
| trans_id | paid_by | paid_to | amount | transacted_on |
| -------- | ------- | ------- | ------ | ------------- |
| 1 | 2 | 4 | 1000 | 2020-08-28 |
| 2 | 3 | 2 | 600 | 2020-08-06 |
| 3 | 2 | 4 | 800 | 2020-08-15 |
| 4 | 1 | 3 | 800 | 2020-09-02 |
| 5 | 3 | 4 | 100 | 2020-08-02 |
| 6 | 3 | 4 | 500 | 2020-08-08 |
| 7 | 3 | 4 | 800 | 2020-09-17 |
My code:
WITH tb1 AS (SELECT u.user_id, u.user_name, u.credit AS initial_amount,
SUM(t1.amount) AS credit_out, SUM(t2.amount) AS credit_in
FROM Users u
LEFT JOIN Transactions t1
ON u.user_id = t1.paid_by
LEFT JOIN Transactions t2
ON u.user_id = t2.paid_to
GROUP BY u.user_id),
tb2 AS (SELECT user_id, user_name,
(initial_amount - IFNULL(credit_out,0) IFNULL(credit_in,0)) AS credit
FROM tb1)
SELECT user_id,user_name,credit,
CASE WHEN credit < 0 THEN 'Yes' ELSE 'No' END AS credit_limit_breached
FROM tb2
My thought is first to join the Transaction table to Users based on paid_by and paid_to to and calculate the credit in and out during these transactions. And then sum the initial_amount, credit_in and credit_out to get a final credit in the users' account.
My output is:
| user_id | user_name | credit | credit_limit_breached |
| ------- | --------- | ------ | --------------------- |
| 1 | Winston | -700 | Yes |
| 2 | Moustafa | 0 | No |
| 3 | Jonathan | 2000 | No |
| 4 | Maria | 3300 | No |
And the expected output is:
| user_id | user_name | credit | credit_limit_breached |
| ------- | --------- | ------ | --------------------- |
| 2 | Moustafa | -600 | Yes |
| 3 | Jonathan | -400 | Yes |
| 1 | Winston | -700 | Yes |
| 4 | Maria | 3300 | No |
I cannot figure out which step of my code is wrong and why am I getting these credit calculations...
CodePudding user response:
The problem is about join
. If you decompose the first CTE query:
SELECT u.user_id, u.user_name, u.credit AS initial_amount, t1.amount AS credit_out, t2.amount AS credit_in
FROM Users u
LEFT JOIN Transactions t1
ON u.user_id = t1.paid_by
LEFT JOIN Transactions t2
ON u.user_id = t2.paid_to
WHERE u.user_id = 2
You are not getting a single row here, you are getting the t1 x t2 rows with duplicated values. And this is what is beeing aggregated providing incorrect SUM
results. Try a simpler query w/o CTE or extra joins:
SELECT
overall.user_id,
Users.user_name,
SUM(overall.amount) as balance,
IF( SUM(overall.amount) < 0 , "Yes", "No") as credit_limit_breached
FROM(
SELECT outbount.paid_by as user_id, SUM(outbount.amount) * -1 as amount
FROM Transactions outbount
GROUP BY paid_by
UNION ALL
SELECT inbound.paid_to as user_id, SUM(inbound.amount) as amount
FROM Transactions inbound
GROUP BY paid_to
UNION ALL
SELECT initial.user_id as user_id, initial.credit as amount
FROM Users initial
) overall
LEFT JOIN Users
ON overall.user_id = Users.user_id
GROUP BY user_id
CodePudding user response:
You could aggregate then join as the following:
With get_credit As
(
Select paid_to, SUM(amount) AS credit
From Transactions Group BY paid_to
),
get_dept AS
(
Select paid_by, SUM(amount) AS depit
From Transactions Group BY paid_by
)
Select U.user_id, U.user_name,
U.credit Coalesce(C.credit, 0) - Coalesce(D.depit, 0) As credit,
Case
When U.credit Coalesce(C.credit, 0) - Coalesce(D.depit, 0) > 0
Then 'No' Else 'Yes'
End As credit_limit_breached
From Users U Left Join get_credit C
ON U.user_id = C.paid_to
Left Join get_dept D
ON U.user_id = D.paid_by
Order By U.user_id
See demo.