银行账户概要 II

1587. 银行账户概要 II

表: Users

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| account      | int     |
| name         | varchar |
+--------------+---------+
account 是该表的主键.
表中的每一行包含银行里中每一个用户的账号.

表: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trans_id      | int     |
| account       | int     |
| amount        | int     |
| transacted_on | date    |
+---------------+---------+
trans_id 是该表主键.
该表的每一行包含了所有账户的交易改变情况.
如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的.
所有账户的起始余额为 0.

写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.

返回结果表单没有顺序要求.

查询结果格式如下例所示.

Users table:
+------------+--------------+
| account    | name         |
+------------+--------------+
| 900001     | Alice        |
| 900002     | Bob          |
| 900003     | Charlie      |
+------------+--------------+

Transactions table:
+------------+------------+------------+---------------+
| trans_id   | account    | amount     | transacted_on |
+------------+------------+------------+---------------+
| 1          | 900001     | 7000       |  2020-08-01   |
| 2          | 900001     | 7000       |  2020-09-01   |
| 3          | 900001     | -3000      |  2020-09-02   |
| 4          | 900002     | 1000       |  2020-09-12   |
| 5          | 900003     | 6000       |  2020-08-07   |
| 6          | 900003     | 6000       |  2020-09-07   |
| 7          | 900003     | -4000      |  2020-09-11   |
+------------+------------+------------+---------------+

Result table:
+------------+------------+
| name       | balance    |
+------------+------------+
| Alice      | 11000      |
+------------+------------+
Alice 的余额为(7000 + 7000 - 3000) = 11000.
Bob 的余额为1000.
Charlie 的余额为(6000 + 6000 - 4000) = 8000.

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

pythondata 解法, 执行用时: 424 ms, 内存消耗: 67.3 MB, 提交时间: 2024-05-27 13:06:12

import pandas as pd

def account_summary2(users: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    transactions = transactions.groupby('account')['amount'].sum().reset_index()
    transactions =transactions[transactions['amount']>10000].rename(columns={'amount':'balance'})
    result = pd.merge(transactions,users,on='account',how='left')
    return result[['name','balance']]

def account_summary(users: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    df= transactions.groupby('account').sum('amount').reset_index()
    df1=pd.merge(df,users).rename(columns={'amount':'balance'})
    return df1[df1['balance']>10000][['name','balance']]

mysql 解法, 执行用时: 773 ms, 内存消耗: 0 B, 提交时间: 2022-05-26 17:50:18

# Write your MySQL query statement below

select u1.name, sum(t1.amount) as balance from Transactions as t1 inner join Users as u1 on t1.account=u1.account group by u1.account having balance>10000;

上一题

下一题

详情