Calculate transaction balance in SQL-CodePudding

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.