Bank Account Summary II

Bank Account Summary II

What is this problem about?

The "Bank Account Summary II interview question" is a fundamental database challenge that focuses on data aggregation and relational linking. In a typical banking database, information is often spread across multiple tables to maintain normalization. You are usually given two tables: a Users table (containing user IDs and names) and a Transactions table (containing transaction details like user ID, amount, and date). The goal is to calculate the total balance for each user and identify those whose balance exceeds a specific threshold (e.g., 10,000).

Why is this asked in interviews?

Companies like Amazon and Google ask the "Bank Account Summary II coding problem" to evaluate a candidate's proficiency in SQL, specifically their ability to perform joins and aggregate functions. It tests whether a candidate can correctly group data, handle multiple transactions per user, and apply filters to the aggregated results (rather than the raw data). This is a core skill for anyone working with backend systems or data analytics.

Algorithmic pattern used

This problem follows the SQL Aggregation and Join pattern.

1. Joining: Use an INNER JOIN to connect the Users table with the Transactions table using the common user_id.
2. Grouping: Use the GROUP BY clause on the user's name (or ID) to collect all transactions belonging to that specific person.
3. Aggregation: Apply the SUM() function to the transaction amounts to find the net balance.
4. Filtering: Use the HAVING clause to filter the results based on the aggregated sum. Note that WHERE cannot be used here because the filter is applied after the aggregation.

Example explanation

Suppose we have the following data:

• Users: (1, Alice), (2, Bob)
• Transactions: (1, 7000), (1, 4000), (2, 5000), (2, -1000)

1. Aggregation:
   • Alice's total = $7000 + 4000 = 11000$.
   • Bob's total = $5000 - 1000 = 4000$.
2. Filtering:
   • If the threshold is 10,000, only Alice is returned. Final Result: Alice, 11000.

Common mistakes candidates make

• Using WHERE instead of HAVING: Attempting to filter the balance in a WHERE clause is a classic syntax error in SQL since the balance is a result of a SUM() function.
• Forgetting the Join: Trying to get names from the transaction table alone (which only contains IDs) or IDs from the user table alone.
• Double Counting: Incorrectly joining tables in a way that duplicates transaction records, though less common in this specific "Database interview pattern."

Interview preparation tip

Always remember the order of execution in a SQL query: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Understanding this sequence makes it clear why HAVING is necessary for filtering aggregated sums.