GROUP BY a composite key + HAVING to find same-day burst activity
Different angle from Teresa's question. I don't care about the monthly structuring signal right now — I want to know which accounts took in 2 or more cash deposits on the same day. Same-day burst is its own typology (the cash came in from one bag split across multiple deposit envelopes). Return account_id, posted_date, and the deposit count, for any (account, day) pair with at least 2 cash deposits. Exclude reversals. Order by count desc then account asc.
| 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.
Group by BOTH `account_id` and `posted_date`. The pair is the grain — a single account gets one row per day.
Filter `channel = 'cash_deposit'` and `is_reversal = 0`. HAVING `COUNT(*) >= 2`.
Each output row is one (account, day) where the cash-deposit count met the gate. Order DESC by count if you want the worst bursts on top.