# Write your MySQL query statement below
1555. 银行账户概要
用户表： Users
+--------------+---------+ | Column Name | Type | +--------------+---------+ | user_id | int | | user_name | varchar | | credit | int | +--------------+---------+ user_id 是这个表的主键（具有唯一值的列）。 表中的每一列包含每一个用户当前的额度信息。
交易表：Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | paid_by | int | | paid_to | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id 是这个表的主键（具有唯一值的列）。 表中的每一列包含银行的交易信息。 ID 为 paid_by 的用户给 ID 为 paid_to 的用户转账。
力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。
编写解决方案报告：
user_id 用户 IDuser_name 用户名credit 完成交易后的余额credit_limit_breached 检查是否透支 （"Yes" 或 "No"）以任意顺序返回结果表。
结果格式见如下所示。
示例 1:
输入： Users表： +------------+--------------+-------------+ | user_id | user_name | credit | +------------+--------------+-------------+ | 1 | Moustafa | 100 | | 2 | Jonathan | 200 | | 3 | Winston | 10000 | | 4 | Luis | 800 | +------------+--------------+-------------+Transactions表： +------------+------------+------------+----------+---------------+ | trans_id | paid_by | paid_to | amount | transacted_on | +------------+------------+------------+----------+---------------+ | 1 | 1 | 3 | 400 | 2020-08-01 | | 2 | 3 | 2 | 500 | 2020-08-02 | | 3 | 2 | 1 | 200 | 2020-08-03 | +------------+------------+------------+----------+---------------+ 输出： +------------+------------+------------+-----------------------+ |user_id|user_name|credit|credit_limit_breached| +------------+------------+------------+-----------------------+ | 1 | Moustafa | -100 | Yes | | 2 | Jonathan | 500 | No | | 3 | Winston | 9900 | No | | 4 | Luis | 800 | No | +------------+------------+------------+-----------------------+ Moustafa 在 "2020-08-01" 支付了 $400 并在 "2020-08-03" 收到了 $200 ，当前额度 (100 -400 +200) = -$100 Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ，当前额度 (200 +500 -200) = $500 Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ，当前额度 (10000 +400 -500) = $9900 Luis 未收到任何转账信息，额度 = $800
原站题解
mysql 解法, 执行用时: 534 ms, 内存消耗: 0 B, 提交时间: 2023-10-16 14:37:00
# Write your MySQL query statement below
select u1.user_id USER_ID, u1.user_name USER_NAME, u1.credit - ifnull(p1.sum_out,0) + ifnull(p2.sum_in,0) CREDIT,
if(u1.credit - ifnull(p1.sum_out,0) + ifnull(p2.sum_in,0) < 0,'Yes','No') CREDIT_LIMIT_BREACHED
from Users u1 left join
(
select paid_by,ifnull(sum(amount),0) sum_out
from Transactions group by paid_by
) p1 on u1.USER_ID = p1.paid_by, Users u2 left join
(
select paid_to,ifnull(sum(amount),0) sum_in
from Transactions group by paid_to
) p2 on u2.USER_ID = p2.paid_to
where u1.USER_ID = u2.USER_ID;
pythondata 解法, 执行用时: 344 ms, 内存消耗: 60.5 MB, 提交时间: 2023-10-16 14:30:24
import pandas as pd
def bank_account_summary(users: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
credit_change_by_paid_by = transactions.groupby('paid_by')['amount'].sum().reset_index()
credit_change_by_paid_to = transactions.groupby('paid_to')['amount'].sum().reset_index()
users = users.merge(credit_change_by_paid_by, left_on='user_id', right_on='paid_by', how='left')
users = users.merge(credit_change_by_paid_to, left_on='user_id', right_on='paid_to', how='left')
users['amount_x'].fillna(0, inplace=True)
users['amount_y'].fillna(0, inplace=True)
users['credit'] = users['credit'] - users['amount_x'] + users['amount_y']
users['credit_limit_breached'] = 'No'
users.loc[users['credit'] < 0, 'credit_limit_breached'] = 'Yes'
return users[['user_id', 'user_name', 'credit', 'credit_limit_breached']]