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 270 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: OverviewInput: Users & TransactionsUsersAlice100Bob200TransactionsAlice→Bob50Bob→Alice80JOIN &CALCULATEOutput: Account SummaryusercreditbalancebreachedAlice100130NoBob200150NoCalculation LogicAlice Balance:• Initial Credit: 100• Paid to Bob: -50• Received from Bob: +80• Final: 130 (No breach)Bob Balance:• Initial Credit: 200• Received from Alice: +50• Paid to Alice: -80• Final: 150 (No breach)
Understanding the Visualization
1
Input Tables
Users with initial credits and Transactions
2
Aggregate & Join
Sum transactions per user and join with Users
3
Calculate
Compute balance and breach status
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to include all users and aggregate transactions to calculate net balance changes

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