Aggregate + subquery / HAVING to surface accounts with long-dormant gaps and a sudden large credit
Run the dormant-then-active query. For every account, figure out the gap between its most recent transaction and the one before. I want accounts where the most recent transaction is a cash deposit of at least $10,000, AND that transaction is at least 365 days after the prior transaction on that account. That's the "money mule waking up" pattern — an account that went quiet for a year and then suddenly takes in a big cash amount. Return account_id, the prior_last_date, and the wake_date. Exclude reversals throughout.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
This is a per-account ordering problem. The cleanest approach: use `LAG(posted_date) OVER (PARTITION BY account_id ORDER BY posted_date)` to get the prior transaction's date on each row.
Then in the outer query, filter: the current row must be a `cash_deposit` of `amount >= 10000` with `is_reversal = 0`, AND `(posted_date - prior_date) >= 365` days. Cast the date strings or use julianday() depending on your dialect.
SQLite date math: julianday(posted_date) - julianday(prior_date) >= 365 works against TEXT dates in YYYY-MM-DD format.