Objective

Write a SQL query to calculate the final account balance for each account based on all transactions.

Table Schema:

• transactions

Task Requirements:

• Process all transactions for each account
• Add deposits and subtract withdrawals
• Calculate the final balance for each account
• Sort results by account ID in ascending order
  
  

Output columns: account_id, final_balance

SELECT 
    account_id,
    SUM(CASE 
        WHEN transaction_type = 'deposit' THEN amount
        WHEN transaction_type = 'withdrawal' THEN -amount
        ELSE 0
    END) AS final_balance
FROM transactions
GROUP BY account_id
ORDER BY account_id ASC;

Explanation

Step 1: Map Transaction Types to Signed Amounts

Use a CASE statement to return the amount as positive for deposits and negative for withdrawals.

CASE 
    WHEN transaction_type = 'deposit' THEN amount
    WHEN transaction_type = 'withdrawal' THEN -amount
    ELSE 0
END

Step 2: Sum to Calculate Final Balance

Wrap the CASE expression in SUM to add up all signed transaction amounts per account, yielding the net balance.

SUM(CASE ... END) AS final_balance

Step 3: Group by Account and Sort

Group by account_id to compute each account's balance independently, then order by account_id ascending.

GROUP BY account_id
ORDER BY account_id ASC