Bank Account Summary II

Imagine you're tasked with building a system to generate consolidated financial summaries for bank customers. This challenge requires you to process transaction data and customer information to create a report that shows the total balance for each customer, considering both deposits and withdrawals. This is a fundamental task for financial applications, enabling users to quickly understand their financial standing.

Problem Description

You are given two datasets: one containing information about bank accounts and another containing transaction details. Your goal is to calculate the final balance for each customer who has made at least one deposit. The balance should be the sum of all deposits minus the sum of all withdrawals for that customer.

Key Requirements:

1. Identify Customers with Deposits: Only include customers who have made at least one deposit. Customers who only have withdrawals should be excluded from the final summary.
2. Calculate Net Balance: For each eligible customer, calculate their net balance by summing all their deposit amounts and subtracting the sum of all their withdrawal amounts.
3. Handle Zero Balance: A customer might have deposits and withdrawals that perfectly cancel each other out, resulting in a zero balance. These customers should still be included if they meet the deposit criteria.
4. Handle Customers with No Transactions: If a customer exists in the Accounts table but has no corresponding transactions in the Transactions table, they should not appear in the output, as they haven't made any deposits.

Expected Behavior:

The output should be a list or table of customers and their calculated net balances. Each entry should represent a unique customer who has made at least one deposit.

Edge Cases:

• A customer might have multiple deposits and multiple withdrawals.
• A customer might have only deposits and no withdrawals.
• A customer might have deposits and withdrawals that result in a positive, negative, or zero balance.
• Transactions might occur out of chronological order; their order does not affect the final balance calculation.

Examples

Example 1:

Input:

Accounts Table:
+-------------+------+
| account_id  | name |
+-------------+------+
| 1           | Alice|
| 2           | Bob  |
| 3           | Carol|
| 4           | David|
+-------------+------+

Transactions Table:
+-------------+------------+----------+
| transaction_id | account_id | amount   |
+-------------+------------+----------+
| 1           | 1          | 100      |  -- Deposit
| 2           | 1          | -50      |  -- Withdrawal
| 3           | 2          | 200      |  -- Deposit
| 4           | 3          | -75      |  -- Withdrawal
| 5           | 3          | 150      |  -- Deposit
| 6           | 1          | 25       |  -- Deposit
+-------------+------------+----------+

Output:
+------+--------+
| name | balance|
+------+--------+
| Alice| 75     |
| Carol| 75     |
+------+--------+

Explanation:
- Alice (account_id 1): Has deposits (100, 25) and a withdrawal (-50). Total deposits = 125, Total withdrawals = 50. Net balance = 125 - 50 = 75. Alice is included because she has deposits.
- Bob (account_id 2): Has a deposit (200). Total deposits = 200, Total withdrawals = 0. Net balance = 200 - 0 = 200. (Wait, example output is wrong here, Bob should be included with 200. Let's re-examine the problem statement: "customers who have made at least one deposit". Yes, Bob qualifies. Correcting the output for clarity).
- Carol (account_id 3): Has a deposit (150) and a withdrawal (-75). Total deposits = 150, Total withdrawals = 75. Net balance = 150 - 75 = 75. Carol is included because she has deposits.
- David (account_id 4): Has no transactions. Not included.

**Corrected Output for Example 1:**
+------+--------+
| name | balance|
+------+--------+
| Alice| 75     |
| Bob  | 200    |
| Carol| 75     |
+------+--------+

Example 2:

Input:

Accounts Table:
+-------------+------+
| account_id  | name |
+-------------+------+
| 1           | Eve  |
| 2           | Frank|
+-------------+------+

Transactions Table:
+-------------+------------+----------+
| transaction_id | account_id | amount   |
+-------------+------------+----------+
| 1           | 1          | -100     |  -- Withdrawal
| 2           | 2          | 50       |  -- Deposit
| 3           | 2          | -50      |  -- Withdrawal
+-------------+------------+----------+

Output:
+------+--------+
| name | balance|
+------+--------+
| Frank| 0      |
+------+--------+

Explanation:
- Eve (account_id 1): Has only a withdrawal (-100). Eve is not included because she has no deposits.
- Frank (account_id 2): Has a deposit (50) and a withdrawal (-50). Total deposits = 50, Total withdrawals = 50. Net balance = 50 - 50 = 0. Frank is included because he has a deposit.

Example 3: Customer with only deposits

Input:

Accounts Table:
+-------------+------+
| account_id  | name |
+-------------+------+
| 1           | Grace|
+-------------+------+

Transactions Table:
+-------------+------------+----------+
| transaction_id | account_id | amount   |
+-------------+------------+----------+
| 1           | 1          | 300      |  -- Deposit
| 2           | 1          | 150      |  -- Deposit
+-------------+------------+----------+

Output:
+------+--------+
| name | balance|
+------+--------+
| Grace| 450    |
+------+--------+

Explanation:
- Grace (account_id 1): Has two deposits (300, 150) and no withdrawals. Total deposits = 450, Total withdrawals = 0. Net balance = 450 - 0 = 450. Grace is included because she has deposits.

Constraints

• The Accounts table will have at least one row.
• The Transactions table can be empty.
• account_id is an integer and uniquely identifies an account.
• name is a string representing the customer's name.
• transaction_id is an integer and uniquely identifies a transaction.
• amount is an integer. Positive values represent deposits, and negative values represent withdrawals.
• The amount will be within the range of -10,000 to 10,000.
• The total number of rows in Accounts will be between 1 and 1,000.
• The total number of rows in Transactions will be between 0 and 10,000.
• Your solution should be efficient, aiming for a time complexity that scales reasonably with the input size.

Notes

• Consider how you will group transactions by account_id.
• Think about how to distinguish deposits from withdrawals based on the amount field.
• You will need to join the Accounts table with the Transactions table to get customer names associated with transactions.
• A common approach involves using aggregation functions (like SUM) and filtering based on conditions.
• The problem statement implies that the amount directly reflects the change in balance, so a positive amount adds to the balance and a negative amount subtracts.