SOUNDEX-style name dedup across customer_ids

The 30+ DPD ticked up — and so did the SARs

A consumer-banking AML schema with planted rings, structuring patterns, and device reuse. Sub-$10K cash deposits that stack to CTR-threshold evasion over 14 days, device fingerprints and IPs shared across supposedly separate customers, dormant-then-active money mules, round-dollar wire cycles, and alert dispositions with honest false-positive noise. The exact data shape investigators see on day one.

The SQL patterns AML analysts, fraud strategists, and transaction monitoring investigators actually write — velocity windows, self-joins for counterparty rings, fuzzy entity resolution across customer records, structuring detection, sanctions false-positive tuning, and rule backtests — against a consumer-banking dataset with planted rings, structuring, device reuse, and dormant-then-active patterns. Written for career changers with any bachelor’s degree — AML RightSource, Chainalysis, fintech compliance teams, and AML consultancies all say “SQL + CAMS” and hire on portfolio.

Structuring below the $10K CTR threshold, smurfing across multiple accounts, round-dollar wire cycles, dormant-then-active money mules, and device/IP/email reuse across “separate” customers — every FinCEN / FATF / Wolfsberg pattern maps to a mission.

Real AML Typologies

COUNT() OVER with date-range window frames, correlated subqueries for same-counterparty pairs, SOUNDEX / LEVENSHTEIN for entity resolution, and gap-and-island logic for dormant account reactivation.

Velocity + Self-Join SQL

Backtest a proposed threshold against historical alerts, compute rule precision / recall on true-positive labels, and measure false-positive reduction — the analysis every compliance team asks of new hires.

Rule Tuning + Backtesting

Every mission maps to a published AML analyst prompt (Capital One, Chainalysis, Chime, Block, Ramp, Mercury) and the work real investigators do. Pair with CAMS (~$1,400, 3 months) and you have a hiring cheat code.

Interview + Job-Ready

Ten $9,500 deposits in two weeks.

Meet the customer book

What channels do we see?

Active customers by state

Drop the reversals

Cash deposits over $10K

Sub-$10K cash deposits by customer

Daily deposit count per account

Dormant accounts that just woke up

Customers with no KYC refresh in 3 years

Wire vs cash — volume by channel group

Round-dollar wires — flag a pattern

Velocity — 5+ cash deposits in 24 hours

Structuring candidates — $9K–$9,999 over 14 days

Counterparty ring — same two customers, repeated

Device reuse across "separate" customers

Email reuse — fuzzy entity resolution

Alert precision — how good is our rule?

False positives by rule — tuning targets

Mule network — one receives, many send

Structuring ring capstone — everyone in the network

CTR / SAR filing candidate list

Sanctions-list fuzzy match scorecard

Rule-tuning backtest — precision quadrants

The investigator's dashboard

Multi-signal customer risk score

Cash-intensive customer profile

Rule precision scorecard with rank

Sanctions fuzzy match scorecard

Investigator capstone — case-load board

customer_id

first_name

last_name

date_of_birth

email

phone

state

status

onboarded_date

last_kyc_refresh

aml_customers

account_id

product_type

open_date

close_date

aml_accounts

txn_id

posted_date

posted_ts

amount

channel

counterparty_name

counterparty_account_id

is_reversal

aml_transactions

session_id

login_ts

device_fingerprint

ip_address

email_used

aml_device_sessions

alert_id

rule_name

triggered_at

disposition

aml_alerts

entry_id

full_name

alias

birth_year

country

list_program

added_date

aml_sanctions_list

SOUNDEX-style name dedup across customer_ids

The Brief

You'll practice