SQL Window Functions for Transaction Monitoring

Velocity: the core AML query

SELECT account_id, COUNT(*) AS tx_count, SUM(amount) AS total
FROM transactions
WHERE ts >= NOW() - INTERVAL '1 day'
GROUP BY account_id
HAVING tx_count > 10;

Structuring detection needs rolling windows — window functions handle every time range in one scan. The fixed-window version above is the first cut, but structuring is definitionally boundary-avoidant: a criminal moving money just under a daily threshold will split transfers across the midnight boundary, defeating a daily GROUP BY. That is why the rolling version below is the production query.

Rolling sum via windows

SELECT *,
 SUM(amount) OVER (
 PARTITION BY account_id ORDER BY ts
 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
 ) AS rolling_30d
FROM transactions;

ROWS BETWEEN N PRECEDING AND CURRENT ROW counts exactly N transactions, not N days — for a true 30-day window use RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW, which includes every transaction in the window regardless of count. The distinction is the most common window-function bug in AML queries, and it changes alert behaviour materially for high-frequency accounts.

First transaction from new device

WITH ranked AS (
 SELECT *, ROW_NUMBER() OVER (
 PARTITION BY account_id, device_id ORDER BY ts
 ) AS rn
 FROM transactions
)
SELECT * FROM ranked WHERE rn = 1;

First-use-of-device patterns feed anomaly detectors and are cheap to compute partition-wise. Combine with peer-group baselines: a device ratio far above the account cohort's median is a stronger signal than the raw first-use event. Note the partition key — account_id, device_id — so rn=1 means "first transaction on this device", not "first transaction ever".

Performance notes

Window functions partition in memory: index (account_id, ts) and keep partitions small. On very large tables, pre-filter by ts before the window, or compute per-account aggregates incrementally with an event-stream engine. Peer-group baselines should come from a precomputed cohort table — joining to a live self-aggregate per alert is a classic query-budget killer.

References

• PostgreSQL Documentation — Window Functions(official docs)
• SQL Performance Explained(book)
• Snowflake Documentation — Window Functions(official docs)