Blog / SQL

Bank Transactions with SQL: How to Sum Amounts, Subtract Fees, and Calculate Exchange Operations

Suppose you have a bank account and you make transactions in different currencies, and you want to know how much money you have spent and received in a particular week, and also how much money you have spent and received in USD.

To do this, you need to run an SQL query on the bank's database. The query you would use is:

SELECT SUM(amount) - SUM(fee) AS total, SUM((amount - fee) / exchange_rate) AS total_usd
FROM transactions
WHERE user_id = 345 AND status = 'success' AND accepted_at BETWEEN '2023-02-13 00:00:00' AND '2023-02-20 00:00:00';

Let's break down this query and explain it step by step.

SELECT is a keyword that is used to select specific columns or calculations from a table. SUM is a function that adds up all the values in a column. AS is used to give an alias to a column or a calculation. FROM is a keyword that is used to specify the table(s) from which to select data. WHERE is a keyword that is used to filter the data based on certain conditions.

Now let's look at the specific parts of the query.

The first line of the query is:

SELECT SUM(amount) - SUM(fee) AS total,

This part of the query is calculating the total amount of money you have spent and received in all currencies during the specified time period. The SUM(amount) function adds up all the amounts of your transactions, and the SUM(fee) function adds up all the fees you have paid for those transactions. By subtracting the total fees from the total amount of transactions, you get the net amount of money you have spent and received during that time period. The AS total part of the query gives a name to this calculation, so that you can refer to it later.

The last line of the query is:

WHERE user_id = 345 AND status = 'success' AND accepted_at BETWEEN '2023-02-13 00:00:00' AND '2023-02-20 00:00:00';

This part of the query filters the transactions based on the following conditions:

user_id = 345 This specifies that you want to see only the transactions for your account.
status = 'success' This specifies that you want to see only the transactions that were successful.
accepted_at BETWEEN '2023-02-13 00:00:00' AND '2023-02-20 00:00:00': This specifies the time period for which you want to see the transactions.

In summary, this SQL query calculates the total amount of money you have spent and received in all currencies during a specified time period, as well as the total amount