SQL交易余额计算结果异常，请求排查代码错误

场景说明 #

需要计算用户交易后的账户余额，涉及两张表：Users（存储用户初始余额）和Transactions（存储交易记录）。

Users表结构 #

| user_id | user_name | credit |
| ------- | --------- | ------ |
| 1       | Winston   | 100    |
| 2       | Moustafa  | 600    |
| 3       | Jonathan  | 800    |
| 4       | Maria     | 100    |

Transactions表结构 #

| 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    |

我的实现代码 #

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

我的思路 #

通过两次左连接Transactions表，分别关联paid_by字段统计总支出、paid_to字段统计总收入，再用初始余额减去总支出、加上总收入得到最终余额，最后判断是否透支。

输出结果对比 #

实际输出 #

| user_id | user_name | credit | credit_limit_breached |
| ------- | --------- | ------ | --------------------- |
| 1       | Winston   | -700   | Yes                   |
| 2       | Moustafa  | 0      | No                    |
| 3       | Jonathan  | 2000   | No                    |
| 4       | Maria     | 3300   | No                    |

预期输出 #

| user_id | user_name | credit | credit_limit_breached |
| ------- | --------- | ------ | --------------------- |
| 2       | Moustafa  | -600   | Yes                   |
| 3       | Jonathan  | -400   | Yes                   |
| 1       | Winston   | -700   | Yes                   |
| 4       | Maria     | 3300   | No                    |

实际计算出的余额和预期不符，找不到代码错误在哪里，求帮忙排查问题。

内容的提问来源于stack exchange，提问作者mmmmmm