DB Exercises
0
← Exercises/
Business Analytics•14 of 14
PrevNext
Business AnalyticsAdvancedPRO

Smurfing Detection: Sub-$10,000 Structured Cash Deposits

Scenario

Under the Bank Secrecy Act, financial institutions must report cash deposits of $10,000 or greater (Currency Transaction Report). Money launderers break deposits into chunks just below $10,000 (e.g. $9,000 to $9,999), a felony pattern known as 'structuring' or 'smurfing'.

Task
Identify bank accounts in `cash_deposits` that made **at least 2** cash deposits between **$9,000.00** and **$9,999.99**. Calculate: 1. `account_id` 2. `suspicious_deposits_count`: Count of deposits in that range 3. `total_structured_amount`: Sum of `deposit_amount` Filter deposits where `deposit_amount BETWEEN 9000.00 AND 9999.99`. Group by `account_id` and use `HAVING COUNT(*) >= 2`. Order results by `total_structured_amount` descending.
Expected Output Format

Suspected smurfing bank accounts with 2 or more sub-$10k deposits.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Smurfing Detection: Sub-$10,000 Structured Cash Deposits” SQL Problem

Business Scenario

Under the Bank Secrecy Act, financial institutions must report cash deposits of $10,000 or greater (Currency Transaction Report). Money launderers break deposits into chunks just below $10,000 (e.g. $9,000 to $9,999), a felony pattern known as 'structuring' or 'smurfing'.

Expected Output & Criteria

Suspected smurfing bank accounts with 2 or more sub-$10k deposits.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE cash_deposits (
        deposit_id INT PRIMARY KEY,
        account_id INT NOT NULL,
        deposit_amount NUMERIC(10, 2) NOT NULL,
        deposit_date DATE NOT NULL
      );

Target SQL Clauses & Operators

WHEREGROUP BYHAVING

Want more Business Analytics practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Business Analytics Track→