Watch the video solution for Bank Account Summary, a medium level problem involving Database. This walkthrough by Everyday Data Science has 1,595 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Users
+--------------+---------+ | Column Name | Type | +--------------+---------+ | user_id | int | | user_name | varchar | | credit | int | +--------------+---------+ user_id is the primary key (column with unique values) for this table. Each row of this table contains the current credit information for each user.
Table: Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | paid_by | int | | paid_to | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id is the primary key (column with unique values) for this table. Each row of this table contains information about the transaction in the bank. User with id (paid_by) transfer money to user with id (paid_to).
Leetcode Bank (LCB) helps its coders in making virtual payments. Our bank records all transactions in the table Transaction, we want to find out the current balance of all users and check whether they have breached their credit limit (If their current credit is less than 0).
Write a solution to report.
user_id,user_name,credit, current balance after performing transactions, andcredit_limit_breached, check credit_limit ("Yes" or "No")Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Users table: +------------+--------------+-------------+ | user_id | user_name | credit | +------------+--------------+-------------+ | 1 | Moustafa | 100 | | 2 | Jonathan | 200 | | 3 | Winston | 10000 | | 4 | Luis | 800 | +------------+--------------+-------------+ Transactions table: +------------+------------+------------+----------+---------------+ | 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 | +------------+------------+------------+----------+---------------+ Output: +------------+------------+------------+-----------------------+ | user_id | user_name | credit | credit_limit_breached | +------------+------------+------------+-----------------------+ | 1 | Moustafa | -100 | Yes | | 2 | Jonathan | 500 | No | | 3 | Winston | 9900 | No | | 4 | Luis | 800 | No | +------------+------------+------------+-----------------------+ Explanation: Moustafa paid $400 on "2020-08-01" and received $200 on "2020-08-03", credit (100 -400 +200) = -$100 Jonathan received $500 on "2020-08-02" and paid $200 on "2020-08-08", credit (200 +500 -200) = $500 Winston received $400 on "2020-08-01" and paid $500 on "2020-08-03", credit (10000 +400 -500) = $9990 Luis did not received any transfer, credit = $800
Problem Overview: The task asks you to generate a bank account summary for every user by analyzing the transaction history. Each transaction either sends money from a user or receives money to a user. You need to compute the net balance for each account based on these transfers.
Approach 1: Aggregate Transactions with Conditional SUM (O(n) time, O(n) space)
The cleanest solution uses SQL aggregation with SUM and conditional logic. Join the Users table with the Transactions table, then compute the balance by adding amounts received and subtracting amounts sent. This is typically done with SUM(CASE WHEN ... THEN amount ELSE -amount END) logic. The query iterates through all transactions once, groups by user, and produces the final balance. This pattern appears frequently in database problems where events modify an account state over time.
The key insight is that a transaction has opposite effects depending on the role of the user. If the user is the payer, the amount decreases the balance. If the user is the receiver, the amount increases the balance. Using conditional aggregation allows both effects to be handled inside a single grouped query instead of running multiple subqueries.
Approach 2: Two Aggregations + Join (O(n) time, O(n) space)
Another way is to calculate incoming and outgoing totals separately. First aggregate all transactions where the user appears as paid_to to compute total incoming funds. Then aggregate transactions where the user appears as paid_by to compute outgoing funds. Finally, join these results with the Users table and subtract outgoing from incoming to get the net balance. This approach separates the logic clearly but requires multiple grouped queries.
This strategy is common in analytical SQL workflows and works well when queries become more complex or when additional metrics are needed. The tradeoff is slightly more query complexity compared to the single-pass conditional aggregation approach.
Recommended for interviews: The conditional aggregation approach is usually preferred. It demonstrates strong SQL fundamentals: joins, CASE expressions, and GROUP BY. Interviewers expect you to recognize that transaction effects can be modeled as positive and negative contributions in one pass. Still, understanding the two-aggregation method shows deeper familiarity with relational query design and patterns used in real-world SQL and database systems.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Conditional SUM with CASE | O(n) | O(n) | Best general solution; computes incoming and outgoing effects in one grouped query |
| Separate Incoming and Outgoing Aggregations | O(n) | O(n) | Useful when calculating multiple financial metrics or when query clarity matters |