Skip to content

The Hidden Threat: Unmasking Business Fraud Patterns with SQL (Quick Start Guide with Sample Code Included)

Fraud is a multi-billion-dollar business problem.

And fraud analytics is something I’ve dealt with in my 15+ years of working in data my fair share of times.

But one project really sticks out in my mind.

Business leaders were constantly dealing with hackers exploiting weaknesses in our security system. It felt like we were at war with invisible enemies, and we were losing. Money walking right out the back door. And I was on the hook to get to the bottom of it.

It was not JUST the monetary loss from these fraud incidents that was a concern.

The larger issue was the strain it was putting on our team:

  • The ever-growing list of fraudulent transactions we had to keep track of
  • The stress from having to constantly look over our shoulders for the next attack
  • The sinking morale of the team, knowing that they were always one step behind the fraudsters

The workload was just too much to bear. We were fighting an uphill battle.

The situation was so dire, it felt like we were on the brink of throwing in the towel and letting the fraudsters have their way. It was an unbearable thought but one we found ourselves considering more and more each day.

But then, I stumbled upon the potential of SQL in detecting and preventing fraud.

And the results? Game-changing.

With SQL, we could:

Suddenly, the battle against fraud didn’t seem so unwinnable.

No more drowning in an endless sea of data. No more sleepless nights worrying about the next attack. No more defeated conversations in the break room.

Instead, we had a powerful tool that transformed our approach:

So I’ve put together a comprehensive guide on how to leverage SQL for fraud detection

I’m sharing the exact methods we used to turn the tide in our battle against fraud.

(by the way, this newsletter was inspired by a survey response from a subscriber like you. Can you take 5 minutes to add your voice? Thanks so much!)

For this case study, pretend you are a data analyst at the ultra-popular automobile company, Drezzla Autos.

Despite having cutting-edge electric vehicles, Drezzla Autos is plagued with fraudulent transactions on its e-commerce platform. Your manager wants you to take charge, find out if there are recurring patterns in these fraudulent transactions, and nip this problem in the bud.

SQL to the rescue.

Step 1: Database Setup

Before you begin querying, you need a database. Let’s create four tables – users, products, transactions, and fraud_report.

Follow along using the sample code for this case study

-- Creating Tables

-- Creating users table
    user_name VARCHAR(100),
    account_created TIMESTAMP,
    location VARCHAR(100)

-- Creating products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(8,2),
    category VARCHAR(50)

-- Creating transactions table
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    product_id INT REFERENCES products(product_id),
    transaction_date TIMESTAMP,
    transaction_amount DECIMAL(10,2),
    is_fraud BOOLEAN

-- Creating fraud_report table
CREATE TABLE fraud_report (
    report_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    transaction_id INT REFERENCES transactions(transaction_id),
    report_date TIMESTAMP,
    reason VARCHAR(200)

Here’s what the tables look like:

And here’s the code to populate the tables:

-- Inserting Sample Data

-- Inserting records into users
INSERT INTO users(user_name, account_created, location) VALUES
('Lionel Mersey', '2022-06-20 08:35:44', 'Buenos Aires, Argentina'),
('Jenson Mutton', '2022-03-14 09:17:22', 'London, United Kingdom'),
('Machael Jaxon', '2021-12-11 10:12:01', 'Los Angeles, USA'),
('Bretony Spares', '2022-09-30 07:28:19', 'Las Vegas, USA'),
('Nelson Dandela', '2023-01-22 06:50:13', 'Johannesburg, South Africa'),
('Malcom Ex', '2021-10-27 14:20:45', 'New York, USA'),
('Cristiano Renaldo', '2023-02-17 11:07:33', 'Madrid, Spain'),
('Serena Walliams', '2022-08-11 12:58:42', 'Paris, France'),
('Usane Colt', '2023-03-28 15:30:56', 'Kingston, Jamaica'),
('Simone Bails', '2023-05-13 08:00:00', 'Texas, USA');

-- Inserting records into products
INSERT INTO products(product_name, price, category) VALUES
('Galactic Glider', 5000.00, 'Space Tourism'),
('Nebula Navigator', 10000.00, 'Space Tourism'),
('Star Sprinter', 8000.00, 'Space Tourism'),
('Ocean Explorer', 2000.00, 'Adventure Sports'),
('Summit Conqueror', 1500.00, 'Adventure Sports'),
('Tidal Thrasher', 1200.00, 'Adventure Sports'),
('Velocity Viper', 75000.00, 'Car Racing'),
('Thunderbolt Torpedo', 85000.00, 'Car Racing'),
('Blitz Bruiser', 60000.00, 'Car Racing'),
('Mach Mayhem', 95000.00, 'Car Racing');

-- Inserting records into transactions
INSERT INTO transactions(user_id, product_id, transaction_date, transaction_amount, is_fraud) VALUES
(1, 7, '2023-01-02 15:30:00', 75000.00, FALSE),
(2, 1, '2023-01-10 16:45:00', 5000.00, FALSE),
(3, 9, '2023-01-25 17:00:00', 60000.00, TRUE),
(4, 2, '2023-02-01 08:00:00', 10000.00, TRUE),
(5, 3, '2023-02-15 09:15:00', 8000.00, FALSE),
(6, 4, '2023-03-03 10:30:00', 2000.00, FALSE),
(7, 5, '2023-03-18 11:45:00', 1500.00, FALSE),
(8, 6, '2023-04-02 13:00:00', 1200.00, FALSE),
(9, 8, '2023-04-20 14:15:00', 85000.00, TRUE),
(10, 10, '2023-05-01 17:45:00', 95000.00, FALSE);

-- Inserting records into fraud_report
INSERT INTO fraud_report(user_id, transaction_id, report_date, reason) VALUES
(3, 3, '2023-02-02 10:00:00', 'Transaction from unknown device'),
(4, 4, '2023-04-21 11:15:00', 'Multiple transactions in short duration'),
(9, 9, '2023-05-17 09:15:00', 'Multiple payment decline');

The rest of the steps are analyzing the transactional data to discover insights for the company.

Step 2: When Do Fraudulent Transactions Occur The Most?

A good place to start with business fraud analysis is WHEN fraudulent transactions occur.

Are they more likely to occur at a particular time of the day or week, or perhaps during a specific month or season?

By answering the WHEN question, you can solve a bunch of gnarly problems:

  • Determine if fraud is more likely during certain hours of the day.
  • Focus more attention on fraud monitoring during those peak times.
  • Uncover ideas for further investigation on why fraud is more prevalent during these hours.

Follow along using the sample code for this case study

SELECT EXTRACT(HOUR FROM transaction_date) AS transaction_hour, COUNT(*) as fraud_count
FROM transactions
WHERE is_fraud = TRUE
GROUP BY transaction_hour
ORDER BY fraud_count DESC;

This query groups all fraudulent transactions by the hour of the day, and then counts the number of fraudulent transactions for each hour.

The result will tell us during which hours of the day fraudulent transactions are most prevalent.



Nice work!! Let’s keep going.

Step 3: What Products Are Most Often Associated With Fraudulent Transactions?

Next, we can turn our attention to our products.

Let’s find out if there are specific items that fraudsters often target.

Follow along using the sample code for this case study

SELECT p.product_name, COUNT(*) as fraud_count
FROM transactions t
JOIN products p ON t.product_id = p.product_id
WHERE t.is_fraud = TRUE
GROUP BY p.product_name
ORDER BY fraud_count DESC;

This query joins the transactions table with the products table to get the product names and then groups the fraudulent transactions by product.

It counts the number of fraudulent transactions associated with each product.

The result will tell us which products are most often associated with fraudulent transactions.


Nebula Navigator1
Blitz Bruiser1
Thunderbolt Torpedo1

From here, we can take it one step further to:

  • Identify which products are most targeted by fraudsters.
  • Find out how much we’re losing to fraudulent transactions.
  • Help our team to tighten security measures for those products.
  • Provide insights for further investigation on why these specific products are more attractive to fraudsters.

Step 4: Are There Specific Users Who Are Frequently Associated with Fraudulent Transactions?

Now comes the challenging part.

You can now dig deeper and explore the possibility that certain users might be repeatedly involved in fraudulent transactions.

Let’s build some SQL to find any recurring patterns in user behavior that might indicate fraudulent activity.

Follow along using the sample code for this case study

SELECT u.user_name, COUNT(*) as fraud_count
FROM transactions t
JOIN users u ON t.user_id = u.user_id
WHERE t.is_fraud = TRUE
GROUP BY u.user_name
ORDER BY fraud_count DESC;

This query joins the transactions table with the users table to get the usernames and then groups the fraudulent transactions by user.

It counts the number of fraudulent transactions associated with each user.

The HAVING clause ensures we only consider users who have more than one fraudulent transaction.

The results will tell us which users are repeatedly involved in fraudulent transactions.

Machael Jaxon2


Now we can dig even deeper.

  • Find users who are repeatedly involved in fraudulent transactions.
  • Provide evidence to take appropriate action against those users.
  • Start figuring out whether these users are part of larger fraud networks.

The queries you’ve built here will help the company get to the bottom of the fraud, for sure.

Quick recap of what you learned in this guide:

  1. Set up your database
  2. Analyze when fraud happens
  3. Find which items are most at risk for fraud
  4. Take a look at potential users that might be taking advantage of vulnerabilities

By taking these steps, you help the team at Drezzla Autos establish patterns, take preventive measures, and secure their e-commerce platform, ultimately reducing fraudulent transactions and boosting their bottom line.

Well done!

Take action

There are two types of data professionals: action-takers and side-liners. Don’t sit on the sidelines waiting for an opportunity to fall in your lap. Take action TODAY by following these next steps:

  1. Run through the code and analysis
  2. Make some tweaks to see how things change
  3. Think of other ways you could apply this to your work
  4. Build a project and add it to your portfolio

Do you have more questions? Let me know by filling out the 5-minute survey!

Leave a Reply

Your email address will not be published. Required fields are marked *