WHERE + COUNT to exclude data-quality noise from a volume count
Our January+February volume report is off. The raw row count from `aml_transactions` includes reversals — when the original wire gets recalled or the cash deposit gets re-booked, the reversing row has `is_reversal = 1` and cancels out the money move, but it still bumps the row count. Give me the count of transactions where `is_reversal = 0`. That's the number we'd actually show the board. Single number.
| 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.
The column `is_reversal` is 0 or 1. Filter it in WHERE — don't use `NOT NULL`, the column isn't nullable.
COUNT(*) on aml_transactions filtered to non-reversals — single row, single column.
If you ever return this kind of count to compliance, pair it with the reversal count separately — that's how you prove the discrepancy explains the gap, not a missing load.