Bank Account Summary

Bank Account Summary

What is this problem about?

The Bank Account Summary interview question is a SQL task. You are typically given a Users table and a Transactions table. You need to calculate the current balance for each user by adding all their incoming transfers and subtracting their outgoing transfers from their initial balance. This Bank Account Summary coding problem is a test of data consolidation across multiple transaction types.

Why is this asked in interviews?

Fintech companies and firms like Optum use this to test a candidate's ability to use UNION ALL or CASE statements to handle debits and credits. It evaluates your skill in joining tables and performing multi-step aggregations to reach a final balance.

Algorithmic pattern used

This follows the Database interview pattern of "Consolidate and Aggregate."

1. Calculate total "sent" money per user from the Transactions table.
2. Calculate total "received" money per user from the Transactions table.
3. Join these with the Users table (which has the initial_balance).
4. Final Balance = initial_balance - total_sent + total_received.

Example explanation

User 1: Initial Balance 100. Trans 1: User 1 sends 30 to User 2. Trans 2: User 3 sends 50 to User 1.

• Total Sent: 30.
• Total Received: 50.
• Balance: 100 - 30 + 50 = 120.

Common mistakes candidates make

• Ignoring initial balance: Only summing transactions and forgetting to add the starting amount.
• Handling NULLs: If a user has no transactions, the SUM() might be NULL. You must use COALESCE(SUM(...), 0) to avoid breaking the math.
• User Missing from Transactions: Forgetting that a user who has never made a transaction still has their initial balance and should be in the output. Use LEFT JOIN on the Users table.

Interview preparation tip

Master the use of COALESCE and IFNULL when performing arithmetic on columns that might have missing data. Also, practice using UNION ALL to combine "sender" and "receiver" rows into a single list of changes before aggregating.