Back to questions
Final Account Balance PayPal SQL Interview Question
Final Account Balance
PayPal SQL Interview Question
Given a table containing information about bank deposits and withdrawals made using Paypal, write a query to retrieve the final account balance for each account, taking into account all the transactions recorded in the table with the assumption that there are no missing transactions.
Table:
| Column Name | Type |
|---|---|
| transaction_id | integer |
| account_id | integer |
| amount | decimal |
| transaction_type | varchar |
Example Input:
| transaction_id | account_id | amount | transaction_type |
|---|---|---|---|
| 123 | 101 | 10.00 | Deposit |
| 124 | 101 | 20.00 | Deposit |
| 125 | 101 | 5.00 | Withdrawal |
| 126 | 201 | 20.00 | Deposit |
| 128 | 201 | 10.00 | Withdrawal |
Example Output:
| account_id | final_balance |
|---|---|
| 101 | 25.00 |
| 201 | 10.00 |
Using account ID 101 as an example, $30.00 was deposited into this account, while $5.00 was withdrawn. Therefore, the final account balance can be calculated as the difference between the total deposits and withdrawals which is $30.00 - $5.00, resulting in a final balance of $25.00.
The dataset you are querying against may have different input & output - this is just an example!
Solution
To determine the final balance of each account, we need to manipulate the relevant fields in the table.
The column contains the transaction values that will help us calculate the final balances. However, using the function alone won't suffice, as we need to account for withdrawals and subtract them appropriately.
To achieve this, we can use a conditional CASE statement to negate the amount for withdrawals based on the value of the column. For deposits, we can directly output the amount, and for withdrawals, we can output the negated amount (-1 * amount).
The query to achieve this would look something like this:
Here's an example of 3 random output rows for account ID 101:
| account_id | transaction_type | balance_amount |
|---|---|---|
| 101 | Deposit | 10.00 |
| 101 | Deposit | 20.00 |
| 101 | Withdrawal | -5.00 |
With the withdrawals negated, we can now aggregate the final balances using the aggregate function. We'll use a clause to group the results by , so that the sums calculated represent the final balances for each account.
The aggregation step would look like this:
The final output for account ID 101 would be something like this:
| account_id | final_balance |
|---|---|
| 101 | 25.00 |