Bank Account Summary - Problem

You are given two tables: Users and Transactions.

The Users table contains information about bank users including their initial credit limit. The Transactions table records all money transfers between users.

Write a SQL query to calculate each user's current balance after all transactions and determine if they have breached their credit limit (balance less than 0).

Requirements:

  • Calculate current balance = initial credit + money received - money paid
  • Check if credit limit is breached (balance < 0)
  • Return: user_id, user_name, credit, current_balance, credit_limit_breached
  • Use 'Yes' or 'No' for credit limit breach status

Table Schema

Users
Column Name Type Description
user_id PK int Primary key, unique user identifier
user_name varchar Name of the user
credit int Initial credit limit for the user
Primary Key: user_id
Transactions
Column Name Type Description
trans_id PK int Primary key, unique transaction identifier
paid_by int User ID who paid money
paid_to int User ID who received money
amount int Amount of money transferred
transacted_on date Date when transaction occurred
Primary Key: trans_id

Input & Output

Example 1 — Basic Transaction Flow
Input Tables:
Users
user_id user_name credit
1 Alice 100
2 Bob 200
3 Charlie 50
Transactions
trans_id paid_by paid_to amount transacted_on
1 1 2 50 2024-01-10
2 2 1 80 2024-01-11
3 3 2 70 2024-01-12
Output:
user_id user_name credit current_balance credit_limit_breached
1 Alice 100 130 No
2 Bob 200 240 No
3 Charlie 50 -20 Yes
💡 Note:

Alice starts with 100 credit, pays 50 to Bob, receives 80 from Bob. Final balance: 100 - 50 + 80 = 130. Bob receives 50 from Alice and 70 from Charlie, pays 80 to Alice: 200 + 50 + 70 - 80 = 240. Charlie pays 70 to Bob: 50 - 70 = -20 (breached).
Example 2 — User with No Transactions
Input Tables:
Users
user_id user_name credit
1 Alice 100
2 Bob 200
Transactions
trans_id paid_by paid_to amount transacted_on
1 1 2 150 2024-01-10
Output:
user_id user_name credit current_balance credit_limit_breached
1 Alice 100 -50 Yes
2 Bob 200 350 No
💡 Note:

Alice pays 150 to Bob, exceeding her 100 credit limit, resulting in -50 balance and breaching the limit. Bob receives 150, bringing his balance to 350 with no breach.

Constraints

  • 1 ≤ user_id ≤ 1000
  • 1 ≤ trans_id ≤ 10000
  • amount > 0
  • paid_by ≠ paid_to

Visualization

Tap to expand
Bank Account Summary INPUT Users Table id name credit 1 Alice 1000 2 Bob 500 3 Carol 200 Transactions Table id paid_by paid_to amount 1 1 2 300 2 2 3 800 3 3 1 100 Money Flow Alice Bob Carol ALGORITHM STEPS 1 Initialize Balance Set balance = credit_limit for each user 2 Process Outgoing Subtract amount when user pays (paid_by) 3 Process Incoming Add amount when user receives (paid_to) 4 Check Breach If balance < 0 then breached = Yes Balance Calculation Alice: 1000 - 300 + 100 = 800 Bob: 500 + 300 - 800 = 0 Carol: 200 + 800 - 100 = 900 (No breach in this case) FINAL RESULT Output Table user_id name balance breach 1 Alice 800 No 2 Bob 0 No 3 Carol 900 No Breach Example If Bob had credit = 200: 200 + 300 - 800 = -300 Balance < 0: Breach = Yes SQL Approach SELECT u.user_id, u.name, credit + COALESCE(recv,0) - COALESCE(paid,0) AS balance, CASE WHEN ... END breach Key Insight: Use LEFT JOINs with aggregated transaction sums to handle users with no transactions. COALESCE handles NULL values when a user has no incoming or outgoing payments. Final balance = credit_limit + sum(received) - sum(paid). Breach occurs when balance < 0. TutorialsPoint - Bank Account Summary | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Goldman Sachs 15 JPMorgan 18
23.5K Views
Medium Frequency
~12 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