/*
Paypal SQL Interview Question

Write a SQL query to retrieve the final account balance for each account by calculating the net amount from deposits and withdrawals.
*/
    
-- Schema Setup
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    account_id INT,
    amount DECIMAL(10,2),
    transaction_type VARCHAR(10) CHECK (transaction_type IN ('Deposit', 'Withdrawal'))
);

INSERT INTO transactions (transaction_id, account_id, amount, transaction_type) VALUES
(1, 101, 1000.00, 'Deposit'),
(2, 101, 500.00, 'Withdrawal'),
(3, 102, 2000.00, 'Deposit'),
(4, 102, 300.00, 'Withdrawal'),
(5, 103, 1500.00, 'Deposit'),
(6, 103, 700.00, 'Withdrawal'),
(7, 104, 5000.00, 'Deposit'),
(8, 104, 1000.00, 'Withdrawal'),
(9, 105, 2500.00, 'Deposit'),
(10, 105, 500.00, 'Withdrawal'),
(11, 106, 1800.00, 'Deposit'),
(12, 106, 300.00, 'Withdrawal');

-- Solution
SELECT
   account_id,
   SUM(CASE WHEN transaction_type = 'Deposit' THEN amount ELSE -amount END) AS net_amount
FROM transactions
GROUP BY account_id;