GROUP BY + HAVING to surface customers structuring below a reporting threshold
New question for the Feb 2025 review. For each customer, count their cash deposits in February where the amount was strictly below $10,000 — that's the structuring zone. Return customer_id + the count, but only for customers with at least 3 such deposits in February. Order by the count descending. Exclude reversals. I'm looking for the short list, not every customer who made a single deposit.
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| account_id | INT | FK → aml_accounts |
| posted_date | TEXT | |
| posted_ts | TEXT | |
| amount | REAL | |
| channel | TEXT | |
| counterparty_name | TEXT | |
| counterparty_account_id | INT | |
| is_reversal | INT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → aml_customers |
| product_type | TEXT | |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Join `aml_transactions` to `aml_accounts` on `account_id` so you can group by customer, not by account. A customer can have 3 accounts — you want one row per customer.
Three filters in WHERE: cash channel, non-reversal, amount strictly below 10K. Then narrow to February 2025 by date range. GROUP BY customer_id and HAVING the deposit count is at least 3.
Result is short — structuring is rare relative to legitimate sub-10K deposits, so a tight result set means the filter is doing its job.