Learning Objectives
- Identify the core fraud and AML tables and explain what each one stores and how financial activity flows across them.
- Distinguish between customer-level, transaction-level, and investigation-level data and explain how each level shapes SQL query design.
- Write SQL queries to detect suspicious transactions, identify unusual behavior patterns, and flag high-risk activity using filters, joins, and aggregations.
- Use SQL to support fraud investigations and AML analysis — including velocity checks, risk scoring, sanctions screening, and alert triage.
SQL for Fraud Detection & Risk Analytics is structured as a progressive journey — from understanding how fraud data is organized to hands-on detection querying to advanced investigation and AML analytics. Each part builds directly on the knowledge established in the previous one, mirroring how fraud and compliance professionals actually work.
How fraud and AML data is organized across core tables — customers, accounts, transactions, merchants, and devices. Learners develop a foundational understanding of how financial activity flows and how key fraud indicators are captured in structured data.
Hands-on querying across multiple tables — joining transactions to accounts, identifying risky merchants, detecting unusual frequency patterns, and using GROUP BY and HAVING to surface abnormal or high-value activity.
Advanced detection and investigation work — transaction anomaly detection, velocity checks, fraud alert and case analysis, customer risk evaluation, AML alert review, sanctions screening, and detecting suspicious relationships across accounts.
By the end of all three parts, learners will be able to work confidently with fraud and AML data — writing queries that detect anomalies, trace suspicious activity, and support real-world financial crime investigations using structured SQL.
Modern financial systems store fraud and AML data across structured tables that capture customer behavior, transaction activity, device access, and risk signals. Every table in this course reflects the real data infrastructure used by banks, fintech platforms, and compliance teams.
Identifying who is behind an account or transaction. KYC (Know Your Customer) status, customer type, and onboarding date are key fraud indicators used to assess baseline risk.
Tracing all financial activity back to specific accounts. Account type, status, and balance changes are critical for detecting account takeover, dormant account abuse, and layering in money laundering schemes.
The primary table for all fraud detection work. Used for high-value transaction filtering, frequency analysis, unusual location detection, velocity checks, and identifying patterns that trigger alerts.
Identifying high-risk merchant categories (e.g., gambling, crypto exchanges), suspicious geographic concentrations, and merchants disproportionately associated with fraud alerts.
Detecting account takeover — new or untrusted devices, logins from unusual locations, multiple accounts sharing an IP address, and abnormal login frequency patterns.
The starting point of most fraud investigations. Used to triage open alerts by priority, identify repeat-alert customers, and link alerts back to specific transactions and accounts.
Tracking investigations from alert to resolution. Used for analyst workload analysis, case outcome reporting, and identifying trends in confirmed fraud versus false positives.
Identifying customers and accounts flagged for suspicious AML patterns — structuring, rapid movement of funds, unusual international transfers, or activity inconsistent with customer profile.
Identifying customers who have matched or closely matched sanctions, PEP (Politically Exposed Person), or watchlist entries — a regulatory requirement for all financial institutions.
Segmenting customers by risk level (low, medium, high), identifying customers whose risk scores have increased over time, and prioritizing which customers require enhanced due diligence.
How the Tables Connect
All ten tables connect through shared keys — customer_id threads through every table, account_id links accounts to transactions and AML alerts, and transaction_id connects transactions to fraud alerts. Understanding these connections is what makes end-to-end fraud investigations possible in SQL.
Before writing any query, always ask: Am I analyzing a customer, a transaction, or an investigation? The answer determines which table to start from and how every join should be structured.
Stores fixed identity and risk attributes — KYC status, customer type, onboarding date. Use for risk profiling, sanctions screening review, and identifying high-risk customer segments.
Stores each individual financial event. One customer generates many transaction-level rows — making this table the primary source for all detection work involving amounts, frequency, and timing.
Stores triggered alerts and opened cases. Links back to specific transactions and customers. Use for triage, workload analysis, and measuring investigation outcomes and resolution rates.
The Full Data Hierarchy
| Level | Table(s) | Relationship | Example Fraud Question |
|---|---|---|---|
| Customer | customers · risk_scores | 1 row per customer | "Which customers have a high risk band?" |
| Account | accounts | Many rows per customer | "Which accounts have had status changes recently?" |
| Transaction | transactions | Many rows per account | "Which transactions exceed $10,000 in the last 7 days?" |
| Alert | fraud_alerts · aml_alerts | Many rows per transaction or customer | "Which open alerts are classified as high priority?" |
| Investigation | fraud_cases | Many rows per alert | "How many cases were resolved as confirmed fraud last quarter?" |
Always verify row counts after joining. A customer joined to their transactions will produce one row per transaction — not one row per customer. Counting customers with COUNT(*) after such a join will overcount. Use COUNT(DISTINCT customer_id) whenever the question is about people, not events.
Fraud and AML analysis is built around a set of core fields that appear across nearly every detection query. Understanding what each field means — and how it behaves — is essential before writing any SQL for financial crime analytics.
Transaction Fields — The Detection Engine
The transactions table fields drive the majority of fraud detection work. Almost every fraud query either filters, aggregates, or compares values from these fields.
| Field | Why It Matters in Fraud Detection |
|---|---|
| amount | High-value transactions are a primary fraud signal. Filtering for amounts above thresholds or deviating from a customer's normal range is a core detection technique. |
| transaction_date | Timestamps power velocity checks — detecting multiple transactions in a short window. Also used for after-hours activity detection and time-of-day pattern analysis. |
| transaction_type | Distinguishes payments, transfers, and withdrawals. Rapid transfers between accounts are a key money laundering indicator. |
| location | Transactions from unusual or high-risk locations — or from multiple countries in a short time window — are strong fraud signals. |
| status | Declined, reversed, or pending transactions often indicate fraud attempts. Declined-then-approved patterns are a common card testing signal. |
Status & Risk Flag Fields
Status fields across multiple tables act as the first line of filtering in fraud queries. Understanding the typical values in each field is essential for accurate detection logic.
| Field | Table | Typical Values | Used To Identify |
|---|---|---|---|
| alert_status | fraud_alerts | Open, Reviewed, Closed, Escalated | Unresolved alerts requiring investigation |
| case_status | fraud_cases | Open, Under Review, Confirmed Fraud, False Positive, Closed | Investigation outcomes and analyst workload |
| risk_band | risk_scores | Low, Medium, High, Critical | Customers requiring enhanced due diligence |
| match_status | sanctions_screening | No Match, Potential Match, Confirmed Match | Customers who may be on prohibited lists |
| is_trusted | devices | TRUE / FALSE | Account access from unrecognized devices |
| kyc_status | customers | Verified, Pending, Failed, Expired | Customers with incomplete or failed identity verification |
Key Questions These Fields Answer
| Fraud Question | Field(s) | Table(s) |
|---|---|---|
| "Which transactions exceed $10,000?" | amount | transactions |
| "Which customers had 5+ transactions in 1 hour?" | transaction_date · customer_id | transactions + accounts |
| "Which open alerts are high priority?" | alert_status · priority | fraud_alerts |
| "Which customers are rated high risk?" | risk_band | risk_scores |
| "Which customers logged in from an untrusted device?" | is_trusted · customer_id | devices |
| "Which customers matched a sanctions list?" | match_status | sanctions_screening |
Fraud and AML data does not live in a single system. Detection signals flow across transaction monitoring platforms, case management tools, compliance databases, and risk scoring engines. Understanding how data moves across these systems clarifies which table to query for each type of analytical question.
Monitors all financial transactions as they occur, applying rules and thresholds to flag suspicious activity. Maps to transactions and fraud_alerts. Examples: FICO Falcon, Actimize, SAS Fraud.
Manages the lifecycle of fraud investigations from alert to resolution. Maps to fraud_alerts and fraud_cases. Examples: Actimize CMS, NICE Actimize, Oracle Financial Services.
Monitors for structuring, layering, and unusual transaction patterns that indicate money laundering. Maps to aml_alerts and transactions. Examples: NICE Actimize AML, Temenos Financial Crime.
Screens customers against global watchlists and maintains ongoing risk scores. Maps to sanctions_screening and risk_scores. Examples: World-Check, Dow Jones Risk & Compliance, LexisNexis.
The Fraud Detection Data Flow
- Customer is onboarded — identity is verified (KYC), a customer record is created, and an initial risk score is assigned. Data lands in customers and risk_scores.
- Account is opened — a financial account is linked to the customer in the accounts table. Device information is recorded in devices when the customer first logs in.
- Transactions occur — every payment, transfer, and purchase is recorded in the transactions table, linked to the account and merchant.
- Monitoring rules fire — when a transaction breaches a rule or threshold, a record is created in fraud_alerts or aml_alerts, linked to the transaction and customer.
- Analyst reviews the alert — an analyst queries the alert and its linked transaction, account, and customer data. If suspicious, a fraud_cases record is opened.
- Investigation is resolved — the case is updated with findings and a resolution. Risk scores may be recalculated and the customer's status updated based on the outcome.
Always identify which stage of the fraud lifecycle — detection, investigation, AML, or compliance — the question belongs to. That stage points directly to the primary table. Every other table in the query either adds context or applies a filter.
Overview — Key Takeaways
Five foundational principles from this introductory section.
Customers, accounts, transactions, merchants, devices, fraud alerts, fraud cases, AML alerts, sanctions screening, and risk scores — each table maps to a distinct stage of the fraud detection and investigation lifecycle.
Customer, transaction, and investigation-level data. Joining correctly across these levels — and using COUNT(DISTINCT) when needed — prevents inflated counts and misleading detection results.
Amount, transaction_date, status, risk_band, alert_status, and is_trusted are the backbone of fraud SQL. These fields answer the most critical questions about what happened, when, and whether it was flagged.
Transaction monitoring, case management, AML platforms, and compliance screening each map to a distinct set of tables. Identifying the correct system domain before writing a query points directly to the right table.
Conceptual clarity prevents analytical errors. A solid understanding of how fraud data is structured — and how activity flows from transaction to alert to investigation — produces accurate, investigation-ready results from the very first query.