Bank Account Summary - Practice Coding Problems

Bank Account Summary - Problem

Database Medium

Bank Account Summary

You're working for LeetCode Bank (LCB), a virtual payment system that helps programmers manage their finances. The bank maintains two key tables:

Users Table: Contains user information and their initial credit limit
Transactions Table: Records all money transfers between users

Your task is to calculate each user's current balance after all transactions and determine if they've breached their credit limit (balance goes below 0).

Goal: Generate a comprehensive bank account summary showing:
• User ID and name
• Original credit limit
• Current balance after all transactions
• Whether credit limit was breached ("Yes" or "No")

Think of it as creating a bank statement that shows if any account is overdrawn!

Input & Output

example_1.sql — Basic Bank Summary

$ 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_balance | credit_limit_breached | |---------|-----------|--------|--------------|-----------------------| | 1 | Moustafa | 100 | -100 | Yes | | 2 | Jonathan | 200 | 0 | No | | 3 | Winston | 10000 | 9900 | No | | 4 | Luis | 800 | 800 | No |

💡 Note: User 1: Started with 100, paid 400 to user 3, received 200 from user 2. Final: 100 - 400 + 200 = -100 (breached). User 2: Started with 200, received 500 from user 3, paid 200 to user 1. Final: 200 + 500 - 200 = 500. User 3: Started with 10000, received 400 from user 1, paid 500 to user 2. Final: 10000 + 400 - 500 = 9900. User 4: No transactions, balance remains 800.

example_2.sql — No Transactions User

$ Input: Users table: | user_id | user_name | credit | |---------|-----------|--------| | 1 | Alice | 500 | | 2 | Bob | 300 | Transactions table: | trans_id | paid_by | paid_to | amount | transacted_on | |----------|---------|---------|--------|--------------| | 1 | 1 | 2 | 100 | 2020-08-01 |

› Output: | user_id | user_name | credit | credit_balance | credit_limit_breached | |---------|-----------|--------|--------------|-----------------------| | 1 | Alice | 500 | 400 | No | | 2 | Bob | 300 | 400 | No |

💡 Note: Alice paid 100 to Bob. Alice: 500 - 100 = 400. Bob: 300 + 100 = 400. Neither user breached their credit limit.

example_3.sql — Multiple Breach Cases

$ Input: Users table: | user_id | user_name | credit | |---------|-----------|--------| | 1 | Charlie | 50 | | 2 | David | 100 | | 3 | Eve | 150 | Transactions table: | trans_id | paid_by | paid_to | amount | transacted_on | |----------|---------|---------|--------|--------------| | 1 | 1 | 2 | 100 | 2020-08-01 | | 2 | 2 | 3 | 150 | 2020-08-02 | | 3 | 3 | 1 | 50 | 2020-08-03 |

› Output: | user_id | user_name | credit | credit_balance | credit_limit_breached | |---------|-----------|--------|--------------|-----------------------| | 1 | Charlie | 50 | 0 | No | | 2 | David | 100 | 50 | No | | 3 | Eve | 150 | 250 | No |

💡 Note: Charlie: 50 - 100 + 50 = 0 (exactly at limit). David: 100 + 100 - 150 = 50. Eve: 150 + 150 - 50 = 250. This shows edge case where balance equals 0 (not breached).

Visualization

Tap to expand

Understanding the Visualization

Initial State

Each user starts with their credit limit as initial balance

Transaction Processing

Money flows between users - outgoing reduces balance, incoming increases it

Balance Calculation

Final balance = Initial credit - Total paid + Total received

Breach Detection

If final balance < 0, the user has breached their credit limit

Key Takeaway

🎯 Key Insight: Use UNION ALL to create a single transaction stream with positive and negative effects, enabling efficient one-pass aggregation instead of multiple subqueries.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass over transactions table with one GROUP BY

✓ Linear Growth

Space Complexity

O(n)

Space for aggregated transaction effects per user

⚡ Linearithmic Space

Constraints

1 ≤ Users.user_id ≤ 10⁴
1 ≤ Transactions.trans_id ≤ 10⁴
0 ≤ Users.credit ≤ 10⁶
1 ≤ Transactions.amount ≤ 10⁴
All user_ids in Transactions exist in Users table
Transactions.paid_by ≠ Transactions.paid_to (no self-payments)

Asked in

GS Goldman Sachs 25 JP JPMorgan 20 PP PayPal 18 SQ Square 15 ST Stripe 12

The optimal solution uses UNION ALL to combine debits (as negative amounts) and credits (as positive amounts) into a single stream, then aggregates with GROUP BY in one pass. This approach achieves O(n) time complexity and is more efficient than multiple subqueries or separate CTEs.

Common Approaches

Approach	Time	Space	Notes
✓ Separate Aggregation with UNION	O(n)	O(n)	Calculate debits and credits separately, then join the results
Single UNION ALL with Aggregation (Optimal)	O(n)	O(n)	Use UNION ALL to combine debits and credits, then aggregate in one pass
Multiple Subqueries Approach	O(n²)	O(n)	Calculate debits and credits using separate subqueries for each user

Separate Aggregation with UNION — Algorithm Steps

Create CTE for total debits (GROUP BY paid_by)
Create CTE for total credits (GROUP BY paid_to)
LEFT JOIN both CTEs with Users table
Handle NULL values with COALESCE
Calculate final balance and breach status

Visualization

Tap to expand

Step-by-Step Walkthrough

Create Debits CTE

GROUP BY paid_by to get total outgoing amounts

Create Credits CTE

GROUP BY paid_to to get total incoming amounts

JOIN with Users

LEFT JOIN both CTEs to Users table

Final Calculation

Calculate balance and breach status

Code -

solution.c — C

// SQL Solution - Two-Pass Hash (CTEs) Approach
char* query = "\
WITH debits AS ( \
    SELECT paid_by as user_id, SUM(amount) as total_debits \
    FROM Transactions \
    GROUP BY paid_by \
), \
credits AS ( \
    SELECT paid_to as user_id, SUM(amount) as total_credits \
    FROM Transactions \
    GROUP BY paid_to \
) \
SELECT \
    u.user_id, \
    u.user_name, \
    u.credit, \
    u.credit - COALESCE(d.total_debits, 0) + COALESCE(c.total_credits, 0) as credit_balance, \
    CASE \
        WHEN u.credit - COALESCE(d.total_debits, 0) + COALESCE(c.total_credits, 0) < 0 \
        THEN 'Yes' \
        ELSE 'No' \
    END as credit_limit_breached \
FROM Users u \
LEFT JOIN debits d ON u.user_id = d.user_id \
LEFT JOIN credits c ON u.user_id = c.user_id \
ORDER BY u.user_id;";

// Execute query

Time & Space Complexity

Time Complexity

⏱️

O(n)

Two passes over transactions table, but linear time

✓ Linear Growth

Space Complexity

O(n)

Space for CTEs storing aggregated results

⚡ Linearithmic Space

Constraints

1 ≤ Users.user_id ≤ 10⁴
1 ≤ Transactions.trans_id ≤ 10⁴
0 ≤ Users.credit ≤ 10⁶
1 ≤ Transactions.amount ≤ 10⁴
All user_ids in Transactions exist in Users table
Transactions.paid_by ≠ Transactions.paid_to (no self-payments)

23.5K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Separate Aggregation with UNION — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler