Title: Mastering Bank Transaction Analysis with Advanced SQL Queries

Bank transaction data offers a wealth of information that, when analyzed properly, can reveal patterns of fraud, unusual account behavior, and other valuable insights. In this blog, we’ll dive deep into a dataset of bank transactions, exploring advanced SQL queries that you can use to detect suspicious activities and analyze transaction patterns. These queries are perfect for anyone looking to take their SQL skills to the next level.

Understanding the Dataset

Before we dive into the SQL queries, let’s take a moment to understand the structure of our dataset. The table contains the following columns:

• step: Represents a unit of time, such as a day or hour.
• type: The type of transaction (e.g., PAYMENT, TRANSFER).
• amount: The amount of money involved in the transaction.
• nameOrig: The ID of the account initiating the transaction.
• oldbalanceOrg: The balance of the originating account before the transaction.
• newbalanceOrig: The balance of the originating account after the transaction.
• nameDest: The ID of the destination account.
• oldbalanceDest: The balance of the destination account before the transaction.
• newbalanceDest: The balance of the destination account after the transaction.
• isFraud: Indicates whether the transaction is fraudulent (1 for fraud, 0 for non-fraud).
• isFlaggedFraud: Indicates whether the transaction was flagged as potentially fraudulent by the system.

Now that we have a basic understanding of the dataset, let’s jump into some SQL queries that can help you uncover meaningful insights.

1. Identifying Suspicious Transfers Involving Zero Balances

Suspicious transactions often involve accounts with zero balances, either before or after the transaction. Identifying such transactions can be a critical step in detecting fraud.

SELECT *
FROM transactions
WHERE type = 'TRANSFER'
  AND newbalanceOrig = 0
  AND newbalanceDest = 0;

This query filters the transactions to find all transfers where both the originating and destination accounts have a balance of zero after the transaction. This scenario could indicate a potential fraud or an attempt to move funds without leaving a trace.

2. Tracking Unusual Transaction Patterns by Customer

Monitoring transaction patterns can help identify unusual behavior. For example, an account making more than three transactions in a single day where the total amount exceeds $10,000 might be worth investigating.

SELECT nameOrig, DATE(step) AS transaction_date, COUNT(*) AS transaction_count, SUM(amount) AS total_amount
FROM transactions
WHERE DATE(step) IN (
  SELECT DATE(step)
  FROM transactions
  GROUP BY nameOrig, DATE(step)
  HAVING COUNT(*) > 3
)
GROUP BY nameOrig, DATE(step)
HAVING SUM(amount) > 10000;

This query identifies customers who have made more than three transactions in a single day with a total amount exceeding $10,000. Such behavior could indicate an attempt to split large transactions into smaller ones to avoid detection.

3. Finding Potential Money Laundering Activities

Money laundering often involves rapid transfers of large amounts between accounts. This query helps you spot such activities by identifying transactions where large amounts are transferred and then immediately moved to another account within a short time frame (e.g., 1 hour).

SELECT t1.transaction_id AS orig_transaction_id, t1.nameOrig AS originator, t1.nameDest AS intermediate_account, 
       t2.transaction_id AS dest_transaction_id, t2.nameDest AS final_account, t1.amount
FROM transactions t1
JOIN transactions t2 ON t1.nameDest = t2.nameOrig
WHERE t1.type = 'TRANSFER'
  AND t2.type = 'TRANSFER'
  AND t1.amount = t2.amount
  AND t2.step BETWEEN t1.step AND t1.step + INTERVAL 1 HOUR;

This query identifies possible money laundering by detecting a pattern where money is transferred to an intermediate account and then quickly transferred to a final account with no change in the amount.

4. Detecting Accounts with Reversed Transactions

Sometimes, fraudulent activity involves making a transaction and then immediately reversing it. This query identifies accounts where a payment or transfer was immediately followed by a reverse transaction of the same amount.

SELECT t1.transaction_id AS orig_transaction_id, t1.nameOrig, t1.amount, t1.type AS orig_type,
       t2.transaction_id AS reversed_transaction_id, t2.type AS reversed_type
FROM transactions t1
JOIN transactions t2 ON t1.nameOrig = t2.nameOrig
WHERE t1.amount = t2.amount
  AND t2.step = t1.step + 1
  AND ((t1.type = 'PAYMENT' AND t2.type = 'PAYMENT_REVERSAL') OR (t1.type = 'TRANSFER' AND t2.type = 'TRANSFER_REVERSAL'));

This query is useful for spotting cases where an account initiates a payment or transfer, only to reverse it in the very next transaction. This could indicate an attempt to obfuscate the transaction trail.

5. Aggregating Fraudulent Activities by Account

Understanding how fraud is distributed across different accounts can provide insights into where to focus your attention. This query summarizes the total fraudulent amount and the number of fraudulent transactions by the originating account.

SELECT nameOrig, COUNT(*) AS fraud_transaction_count, SUM(amount) AS total_fraud_amount
FROM transactions
WHERE isFraud = 1
GROUP BY nameOrig
HAVING COUNT(*) > 1
ORDER BY total_fraud_amount DESC;

This query aggregates fraudulent activities by originating account, showing the total amount of fraud and the number of fraudulent transactions. It’s especially useful for identifying accounts that are repeatedly involved in fraudulent activities.

6. Identifying Unusual Patterns in Repeated Transactions

Repeated transactions of the same amount to the same destination within a short time frame can indicate automated or suspicious behavior. This query identifies such patterns.

WITH RepeatedTransactions AS (
    SELECT nameOrig, nameDest, amount, COUNT(*) AS transaction_count, MIN(step) AS first_transaction_time
    FROM transactions
    GROUP BY nameOrig, nameDest, amount
    HAVING COUNT(*) > 1
)
SELECT *
FROM RepeatedTransactions rt
JOIN transactions t ON rt.nameOrig = t.nameOrig AND rt.nameDest = t.nameDest AND rt.amount = t.amount
WHERE t.step BETWEEN rt.first_transaction_time AND rt.first_transaction_time + INTERVAL 10 MINUTE;

This query is particularly useful for catching transactions that might be part of a bot or automated process, as it filters out those repeated in a short span.

7. Detecting Account Balances at Risk of Fraud

Accounts with balances that drop significantly after a transaction are often at risk. This query identifies such accounts by comparing post-transaction balances with the average balance.

WITH AvgBalance AS (
    SELECT nameOrig, AVG(oldbalanceOrg) AS avg_balance
    FROM transactions
    GROUP BY nameOrig
)
SELECT t.nameOrig, t.amount, t.newbalanceOrig, ab.avg_balance
FROM transactions t
JOIN AvgBalance ab ON t.nameOrig = ab.nameOrig
WHERE t.newbalanceOrig < ab.avg_balance * 0.5;

This query finds accounts where the post-transaction balance is less than 50% of the average balance for that account, indicating a potentially risky or unusual transaction.

8. Flagging Transactions with Multiple Destinations

Accounts that send money to multiple different destinations within a single day might be involved in suspicious activities. This query helps identify such accounts.

SELECT nameOrig, DATE(step) AS transaction_date, COUNT(DISTINCT nameDest) AS distinct_destinations
FROM transactions
GROUP BY nameOrig, DATE(step)
HAVING COUNT(DISTINCT nameDest) > 3;

This query identifies accounts that have sent money to more than three different destination accounts within a single day, a behavior that could indicate money laundering or other suspicious activities.

9. Tracking Large Transactions Followed by Withdrawals

Fraudsters might transfer large sums to an account and then immediately withdraw the money. This query identifies such patterns by tracking large transfer transactions followed by withdrawals.

WITH LargeTransfers AS (
    SELECT transaction_id, nameOrig, nameDest, amount, step
    FROM transactions
    WHERE type = 'TRANSFER' AND amount > 10000
)
SELECT lt.*, t2.*
FROM LargeTransfers lt
JOIN transactions t2 ON lt.nameDest = t2.nameOrig
WHERE t2.type = 'WITHDRAWAL' AND t2.step = lt.step + 1;

This query is useful for spotting large transfers where the recipient quickly withdraws the money in the next transaction, which can be a red flag for money laundering.

10. Analyzing Fraud Patterns by Transaction Type

Understanding the fraud rate by transaction type can help in prioritizing the analysis of certain types of transactions. This query calculates the percentage of fraudulent transactions for each type.

SELECT type, 
       COUNT(*) AS total_transactions,
       SUM(isFraud) AS fraud_transactions,
       (SUM(isFraud) / COUNT(*) * 100) AS fraud_rate_percentage
FROM transactions
GROUP BY type
ORDER BY fraud_rate_percentage DESC;

This query calculates the fraud rate for each transaction type by comparing the number of fraudulent transactions to the total number of transactions of that type. It’s a powerful tool for focusing your attention on the most problematic transaction types.