Anomaly and Fraud Detection Questions

Direct answer
For each user, count distinct destination accounts and sum transaction amounts over a 7-day trailing window using a date-bounded group-by query; to keep this fast at billions of rows, the table needs to be partitioned by time (so the 7-day window only scans a handful of recent partitions) and indexed on user_id within each partition, rather than relying on a full-table scan.

Structured elaboration and worked example (executed)

SELECT user_id,
       COUNT(DISTINCT dest_account) AS unique_dest_count,
       SUM(amount) AS sum_amount_7d
FROM transactions
WHERE occurred_at >= datetime('now', '-7 days')
  AND occurred_at <= 'now'
GROUP BY user_id
ORDER BY user_id

Executed against an in-memory SQLite table with 6 rows across 2 users (one user with 4 transactions inside the trailing window, one user with one transaction inside the window and one older transaction just outside it):

(100, 3, 545.0)
(200, 1, 30.0)

User 100's 3 in-window transactions to destinations A, B, and C correctly summed to 545.0 across 3 distinct destinations; user 200's older, out-of-window transaction was correctly excluded, leaving only the 30.0 transaction inside the window. The date-boundary logic in the WHERE clause was the part most likely to silently include or exclude the wrong rows, and this run confirms it draws the line exactly at 7 days back from "now."

Keeping it fast at billions of rows

• Time-based partitioning: partition the table by day (or hour, for very high volume), so a 7-day query only needs to touch 7 to 8 partitions instead of scanning the entire history; this is the single highest-leverage change, since it turns an O(table size) scan into an O(window size) scan.
• A user_id index within each partition: since the query groups by user_id, an index on user_id (ideally covering dest_account and amount too, if the engine supports covering indexes) lets each partition's relevant rows be located directly rather than scanned in full.
• Precomputed rolling aggregates as an alternative: at very high query volume, it's often cheaper to maintain a continuously-updated rolling aggregate table (updated incrementally as new transactions arrive) than to recompute a 7-day window from raw rows on every request; this trades storage and update complexity for much lower read latency, which is usually the right trade for a feature that's read far more often than the underlying data changes.

Trade-offs and pitfalls
Partitioning strategy needs to match the query pattern: partitioning by user_id instead of time would make this specific 7-day-window query slower, not faster, since it would have to touch every partition to find each user's recent rows. Always design the partition key around the query the feature actually needs, not just around what feels like a natural key for the data.

Direct answer
Design a randomized experiment that compares the model with and without the new feature on the SAME population, using a metric that's insensitive to the fraud label's delay (for example, agreement with eventual confirmed outcomes measured only once enough time has passed for both arms' cases to be equally confirmed), rather than comparing performance measured at two different points in time, which would confound the feature's effect with any other change that happened between those two points.

Structured elaboration

• Experimental setup: run the model with the new feature and the model without it side by side on the SAME live traffic (for example, computing both scores for every transaction but only acting on one, chosen by randomization, or running both as shadow scores and comparing their agreement with eventual outcomes), rather than a before/after comparison, which would be confounded by any other change (a rule update, a shift in fraud patterns) happening in between the two periods.
• Sampling: since the new feature's real value shows up specifically on the subset of cases where it changes the decision, oversample or specifically examine the disagreement set (cases where the two model versions produce meaningfully different scores) for manual review, rather than relying purely on an aggregate metric across all traffic, most of which the new feature won't meaningfully affect either way.
• Metric choice: track a metric tied to eventual CONFIRMED outcomes (recall and precision measured only after the label-delay window has passed) rather than an immediate proxy metric, since an immediate proxy could show a spurious improvement or degradation that doesn't hold up once real confirmations arrive.
• Keeping statistical validity under label delay: define the experiment's evaluation window explicitly to end only once you'd expect confirmations for that period to be largely complete (see the separate question on label delay for how to estimate that window), and pre-register a fixed sample size or duration for the comparison, rather than "peeking" at partial results and extending or cutting the experiment based on what you're seeing, which inflates the risk of a false positive conclusion about the feature's value.

Trade-offs and pitfalls
The most common way this kind of experiment goes wrong is checking results too early, before the label-delay window has meaningfully closed, and concluding a lift or no-lift that partial, still-incomplete confirmations don't actually support; write down your evaluation window and stopping rule before looking at any results, exactly as you would for any other statistically-rigorous experiment. Also weigh the ENGINEERING cost side explicitly, a feature that adds meaningful serving latency or pipeline complexity needs a correspondingly larger detection lift to be worth shipping, not just any statistically-detectable improvement.

Direct answer
Defending against adversarial evasion means treating your fraud model as a target that will be probed and adapted against, not a static classifier: detect evasion by watching for shifts in the DISTRIBUTION of flagged and missed cases over time (not just an aggregate metric), harden the model and features against known evasion patterns, and have a rehearsed recovery plan for when you confirm an active attack.

Structured elaboration

• Detecting evasion: a drop in overall detection rate can have many causes, so look specifically for signs that an adversary has found a gap: a cluster of confirmed-fraud cases (discovered later, via chargebacks) that all scored suspiciously low from the model, a sudden shift in the distribution of feature values among borderline-scored transactions, or repeated near-identical transaction patterns that sit just under your alerting threshold.
• Hardening the model and features: avoid relying on any single feature an attacker could easily observe and route around (for example, a hard IP-based rule is trivially defeated by a proxy); prefer features that are expensive or slow for an attacker to fake, like device-fingerprint or behavioral-biometric signals, and periodically retrain on adversarially-adapted recent data rather than a static historical snapshot, so the model doesn't keep defending against last year's attack.
• Monitoring for the earliest signs: track the SCORE DISTRIBUTION of confirmed-fraud cases over time (not just overall precision/recall), since a rising share of confirmed fraud scoring just below your threshold is often the earliest sign of a targeted evasion attempt, well before it shows up in an aggregate recall number.
• Recovery once confirmed: isolate the specific evasion pattern (what features or behaviors let it through), add a targeted rule or feature as an immediate patch while a proper model retrain is in progress, and retrain the model on the newly-confirmed cases so the underlying model, not just a rule patch, actually learns the new pattern.

Trade-offs and pitfalls
There is a real tension between hardening a model against evasion and keeping it interpretable and fast: adding many defensive features and ensembling multiple models increases robustness but also increases latency and makes root-cause analysis harder when something does go wrong. It's also a mistake to treat any single hardening technique as a permanent fix, since an adversary who is actively adapting will eventually probe around it too; the realistic goal is raising the cost of evasion continuously, not eliminating it once.

Direct answer
Without ground truth, evaluate an unsupervised detector indirectly: inject synthetic anomalies with a known answer to measure whether the detector actually catches things it should, monitor the stability of its output distribution over time as a proxy for degradation, and set a concrete, pre-agreed trigger (not a vague "if it looks off") for escalating to human review or retraining.

Structured elaboration

• Synthetic anomaly injection: take a sample of real (presumed-legitimate) data, deliberately inject known synthetic anomalies into it (for example, artificially spike a few values, or splice in feature combinations known to resemble past fraud patterns), and check what fraction the detector actually flags. This gives you a recall-like measure against a KNOWN ground truth, even though it's synthetic, and repeating it periodically over time tells you whether detection capability is degrading.
• Stability checks on the detector's own output: track the distribution of anomaly scores it produces over time (not comparing to any external label, just watching itself). A sudden shift in the score distribution, for example a rising fraction of "borderline" scores clustered just under the threshold, often signals either a genuine change in the underlying data or a detector that is starting to miss things, and is worth investigating either way.
• Prioritizing flagged cases for a limited review budget: not every flagged anomaly deserves equal reviewer attention, so combine the anomaly score with a severity or business-impact estimate (transaction size, account value) to rank flagged cases, rather than handing reviewers a raw, unranked list.
• Concrete escalation trigger: define in advance what counts as "enough" evidence of degradation, for example a sustained drop in synthetic-injection recall below a set floor over two consecutive evaluation cycles, or the score-distribution shift crossing a statistical threshold, rather than leaving the retraining decision to informal judgment after the fact.

Trade-offs and pitfalls
Synthetic anomalies are only a proxy for real fraud, and a detector can score well against synthetic injections while still missing real, more subtle fraud patterns the synthetic examples don't resemble; treat a good synthetic-injection score as necessary evidence the detector is broadly functioning, not sufficient proof it's catching everything that matters. It's also easy to over-trust stability checks: a detector's output can look stable while it has quietly stopped being useful, if the underlying legitimate traffic itself has drifted in a way that happens to look similar to the detector.

Direct answer
Run the challenger alongside the champion on live traffic without letting it make the actual decision at first (a shadow deployment), compare the two models' AGREEMENT and disagreement patterns immediately, but wait for the label-delay window to close before trusting any comparison of which model was actually more correct, and promote the challenger only once its confirmed-outcome performance on the disagreement cases clears a pre-set bar, not simply because it looks promising on early, still-unconfirmed signals.

Structured elaboration

• Shadow deployment first: have the challenger score every transaction the champion scores, without acting on the challenger's output, so you can compare the two models directly on identical traffic without any risk to production decisions.
• What you can measure immediately, before labels arrive: the RATE and PATTERN of disagreement between the two models (which specific cases they score differently, and by how much), which tells you where the challenger is actually behaving differently, without yet telling you which one is right.
• What you must wait for: which model was actually correct on the disagreement cases requires confirmed outcomes, which, given label delay, means those specific disagreement cases need to age past your confirmation window before you can honestly measure precision and recall on them; resist the temptation to declare a winner based on early, partially-confirmed data.
• Measuring business impact: once confirmed outcomes are in for the disagreement set, quantify the challenger's impact specifically on the cases where it would have changed the outcome (caught fraud the champion missed, or avoided a false positive the champion generated), since on the cases where both models agree, there's no incremental impact to measure either way.
• Safe promotion: promote the challenger fully (or to an increasing percentage of live traffic, in graduated rollout) only once its confirmed performance on the disagreement set clears a pre-defined bar, and keep the ability to roll back quickly if early production performance at scale doesn't match what the shadow comparison predicted.

Trade-offs and pitfalls
Waiting the full label-delay window to make a promotion decision is slower than teams often want, especially under pressure to ship an improvement quickly; a middle path is a graduated rollout (promoting the challenger to a small percentage of live decision-making traffic once its EARLY signals look promising, while still tracking full confirmed-outcome performance before expanding further), which balances shipping speed against the genuine risk of promoting based on incomplete information. It's also worth explicitly deciding, in advance, what "close enough to the champion, no clear winner" means, since not every challenger comparison resolves with a clean, confident answer.