Skip to main content
Talentd

Bank Account Summary - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Optum
Practice this problem

Problem Statement

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, and
  • credit_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

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Conditional SUM with CASEO(n)O(n)Best general solution; computes incoming and outgoing effects in one grouped query
Separate Incoming and Outgoing AggregationsO(n)O(n)Useful when calculating multiple financial metrics or when query clarity matters

Video Solution

OPTUM LeetCode Medium 1555 “Bank Account Summary" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,652 views views

Frequently Asked Questions

Is Bank Account Summary easy or hard?
Bank Account Summary is rated Medium because it requires combining multiple SQL concepts: joins, conditional logic with CASE, and aggregation with GROUP BY. The logic itself is straightforward once you recognize that outgoing transactions subtract from the balance while incoming transactions add to it.
Bank Account Summary Python/Java solution
This problem is designed as a SQL database query rather than a Python or Java algorithm. The typical solution is written in MySQL using JOIN, CASE expressions, and GROUP BY to compute account balances from transaction records.
How to solve Bank Account Summary in O(n)?
Use a single SQL query with GROUP BY and conditional aggregation. Join Users with Transactions and compute the balance using SUM(CASE WHEN paid_to = user_id THEN amount WHEN paid_by = user_id THEN -amount END). This processes each transaction once and aggregates balances per user efficiently.
What is the best approach for Bank Account Summary?
The best approach uses SQL aggregation with a conditional SUM. You join the Users table with Transactions and use a CASE expression to add amounts when the user receives money and subtract amounts when the user sends money. This computes the balance in a single grouped query with O(n) time complexity.
Is Bank Account Summary asked at Google/Amazon/Meta?
Database aggregation problems like Bank Account Summary commonly appear in SQL interview rounds at large tech companies. Variations involving transaction ledgers, financial balances, and conditional aggregation have been reported in interviews at companies such as Amazon and Meta.
What data structure is used in Bank Account Summary?
The problem relies on relational database operations rather than traditional data structures. SQL uses tables, joins, grouping, and aggregation functions like SUM. Conceptually, the GROUP BY operation behaves similarly to a hash aggregation over user IDs.
What is the time complexity of Bank Account Summary?
The SQL solution runs in O(n) time where n is the number of transactions. The database scans the transaction table once, applies conditional aggregation, and groups results by user. Space complexity is O(n) for storing intermediate grouped results depending on the query planner.

Ready to solve this problem?

Practice Bank Account Summary with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary