Articles
SQL Fraud Detection Interview Questions: Velocity Windows, Shared Devices, and False Positives
Practice SQL fraud detection interview questions with velocity windows, shared devices, point-in-time features, false positives, and tested query examples.
SQL fraud detection interview questions test whether you can reconstruct a suspicious pattern without inventing evidence. A useful answer defines the decision timestamp, builds velocity and shared-device features from information available then, and returns explainable review flags. It does not equate a busy customer or a shared laptop with confirmed fraud.
For analyst and data scientist interviews, the central distinction is a correct feature query versus a justified enforcement decision. The original exercise below narrows the problem to two transparent signals and then checks their false-positive implications.
Official sources were checked September 21, 2026. The schema, thresholds, transactions, and evaluation counts are synthetic teaching examples, not deployed fraud rules, provider defaults, or evidence of production effectiveness.
What exactly does a velocity window count?
Velocity measures activity over a bounded time interval. The unit might be payment attempts, distinct cards, recipients, or successful transfers. Those are different signals. Stripe describes velocity checks as a way to identify unusual payment activity over time and also warns that legitimate activity spikes can produce false positives. Stripe velocity-check overview
Our exercise counts prior payment attempts by account in the ten minutes before the current attempt. Every stored row is one distinct attempt, including a declined attempt. Repeated transport delivery of the same event is not another attempt; upstream deduplication preserves its original first-available timestamp.
For an attempt at time t, use [t - 10 minutes, t). The lower boundary is included, and the current event is excluded. All timestamp-equal events are excluded because the fixture does not supply a trusted ordering within one timestamp. A sortable transaction ID is not proof that one event was visible before another.
The second signal counts distinct other accounts that used the current device during the preceding 24 hours. Excluding the current account makes the number interpretable: two other accounts means at least three accounts including the current one. A null device produces zero observed other accounts in this query, but must also be marked as missing evidence.
Why is event time not enough for a historical fraud query?
Suppose a transaction occurred at 10:06 but did not reach the feature system until 10:12. A decision made at 10:10 could not use it. A backtest that reads today's complete warehouse and filters only on event time leaks information unavailable to the original decision.
Record both event_at and available_at, along with the current event's decision_at. Our historical join requires history.available_at < current.decision_at. Strict inequality means data becoming available at exactly the decision timestamp is excluded unless the system supplies a stronger sequencing guarantee.
This contract assumes available_at describes actual feature availability, not merely file creation or warehouse loading. A warehouse ingestion timestamp can be a conservative proxy for an online system, but it does not prove online availability. Say which you have. The current account and device fields must also come from the decision-time event, not a later corrected identity table.
Feature time and label time serve different purposes. You may evaluate a historical prediction using a later confirmed outcome, but you must not put that outcome into the historical feature computation. Keeping those datasets separate is one of the clearest ways to explain leakage prevention.
Worked fixture: which rows can the 10:10 decision see?
Consider account A on device D1 at 10:10 UTC, with a decision at 10:10:01. Its earlier events are:
| Event time | Available | Same account? | Velocity use |
|---|---|---|---|
| 09:59:59 | 10:00:00 | Yes | Outside |
| 10:00:00 | 10:00:01 | Yes | Include |
| 10:04:00 | 10:04:01 | Yes | Include |
| 10:06:00 | 10:12:00 | Yes | Too late |
| 10:09:00 | 10:09:01 | Yes | Include |
| 10:10:00 | 10:10:00 | Yes | Timestamp tie |
The prior-attempt count is three, not five. Separately, B and C used D1 within the prior day and their events were available before the decision. Multiple uses by B still contribute only one distinct other account. A future use by account Z contributes nothing.
For this toy exercise, define a review flag as prior_attempts_10m >= 3 AND other_accounts_24h >= 2. A therefore matches both signals. These numbers are chosen to make the fixture easy to inspect; they are not recommended operational thresholds. Preserve both counts and both individual signal flags in the output so a reviewer can see why the combined rule matched.
If the prompt instead says “at least four attempts including the current one,” state that the current attempt is included and adjust the definition deliberately. Do not switch between inclusive and prior-only counts halfway through your explanation.
SQL solution: build each feature at transaction grain
Use events(event_id, account_id, device_id, event_at, available_at, decision_at). IDs are unique; timestamps are UTC; rows are immutable decision-time records. The query returns one row per event. It is PostgreSQL-style SQL executed against the synthetic fixture in DuckDB, not a live fraud integration.
WITH velocity AS (
SELECT c.event_id, COUNT(h.event_id) AS prior_attempts_10m
FROM events c
LEFT JOIN events h
ON h.account_id = c.account_id
AND h.event_at >= c.event_at - INTERVAL '10 minutes'
AND h.event_at < c.event_at
AND h.available_at < c.decision_at
GROUP BY c.event_id
), shared_device AS (
SELECT c.event_id,
COUNT(DISTINCT h.account_id) AS other_accounts_24h
FROM events c
LEFT JOIN events h
ON c.device_id IS NOT NULL
AND h.device_id = c.device_id
AND h.account_id <> c.account_id
AND h.event_at >= c.event_at - INTERVAL '24 hours'
AND h.event_at < c.event_at
AND h.available_at < c.decision_at
GROUP BY c.event_id
)
SELECT c.event_id,
v.prior_attempts_10m, d.other_accounts_24h,
c.device_id IS NULL AS device_missing,
v.prior_attempts_10m >= 3 AS velocity_signal,
d.other_accounts_24h >= 2 AS device_signal,
(v.prior_attempts_10m >= 3
AND d.other_accounts_24h >= 2) AS review_flag
FROM events c
JOIN velocity v ON v.event_id = c.event_id
JOIN shared_device d ON d.event_id = c.event_id
ORDER BY c.event_id;
The fixture's current event returns 3, 2, false, true, true, true after its ID. Aggregating the two histories independently avoids multiplying account-history rows by device-history rows. If you join both raw histories to the current transaction in one query, three matches on one side and four on the other can create twelve intermediate rows.
COUNT(h.event_id) is intentional. A left join with no history still emits a null-extended row, so COUNT(*) would incorrectly report one. COUNT(DISTINCT h.account_id) handles repeated use of the same device by the same other account; it does not repair duplicated transaction rows in the velocity feature.
No-history accounts remain in the output with zero observed counts. Do not filter them out before evaluating performance, because that would change the population on which false positives and recall are measured. Keep data-quality indicators beside the counts, including missing device coverage and suspicious availability timestamps.
Why not just use a window function?
A window function can be an excellent implementation when its frame matches the requested semantics. But ROWS BETWEEN 9 PRECEDING AND CURRENT ROW counts rows, not ten minutes. A default ordered frame is also not a prior-only time interval and can include timestamp peers. PostgreSQL explicitly documents that ordered default frames include peers equal to the current ordering value. PostgreSQL window tutorial
An interval-based RANGE frame may express the event-time bound, depending on the engine, but you still need to address current-row exclusion, ties, and per-decision data availability. A simple frame over today's completed event history does not automatically solve that last requirement.
For an interview, the bounded self-join is a readable correctness baseline. Then discuss indexes or clustering on (account_id, event_at) and (device_id, event_at), with availability filtering retained. Avoid promising a complexity improvement without inspecting the target engine's plan and data distribution.
When scoring a day's current events, fetch enough preceding history for both features. Filtering the entire input to that day before computing a 24-hour device feature loses yesterday's relevant events. At larger scale, consider incremental keyed state, but explain event deduplication, retention, late arrival, and the online/offline definition before claiming equivalent results.
How can a shared device create false positives?
A household tablet, workplace terminal, or assisted checkout can legitimately serve multiple people. Conversely, an unstable device identifier can split one physical device into several apparent devices. The identifier is a measurement with limitations, not a reliable statement of ownership or intent.
Segment investigation by device coverage and relevant operating context, and inspect whether legitimate high-volume users are disproportionately flagged. A missing identifier should not join every other missing identifier into one enormous device group. The explicit non-null condition prevents that accidental graph.
Keep the response separate from the signal. Review, additional verification, and blocking impose different costs and customer friction. Stripe's rules documentation distinguishes these actions and notes that broad review rules can overload a review queue. Our SQL emits a review candidate; it does not perform any enforcement. Stripe Radar rules
Minimize retained identifiers and restrict access according to the organization's policies. An interview explanation can use synthetic account and device keys, as this example does, without needing customer names, raw device fingerprints, or sensitive personal attributes.
How do you measure false positives when labels arrive later?
Define the positive outcome first. A disputed transaction, a fraud-coded dispute, a confirmed fraudulent transaction, and a lost dispute are not identical labels. Stripe's data documentation exposes dispute reasons and statuses separately; an evaluation must select the intended outcome rather than treating every dispute row as interchangeable ground truth. Stripe dispute and fraud data
For a synthetic, fully labeled sample of 1,000 decisions, suppose 20 are confirmed fraud. The rule flags 30 decisions: 12 fraud and 18 legitimate. That leaves eight false negatives and 962 true negatives. Precision is 12 / 30 = 40%; recall is 12 / 20 = 60%; false-positive rate is 18 / 980, about 1.84%. The review rate is 3%.
Notice that 60% of flags are false discoveries, while only 1.84% of legitimate transactions are false positives. Those denominators answer different questions. A queue manager needs the first; customer-friction analysis needs the second as well as absolute volume. Neither number alone selects a threshold.
Use a defined label-maturity policy and retain unknown outcomes as unknown. Joining current decisions to an incomplete label table and converting every null to “legitimate” misclassifies unresolved outcomes and biases the metrics. Blocked transactions introduce another selection problem: preventing completion can change whether the usual downstream label can ever be observed.
The broader fraud data scientist interview guide covers delayed labels and review budgets in more depth. Here, the essential SQL obligation is preserving decision-time features, decision records, and later evaluation labels as separate, reproducible datasets.
Practice the feature query and the decision explanation
| Practice question | Practice focus |
|---|---|
| Write SQL/Python for ACH fraud analytics | Feature grain |
| Detect k events in a sliding window | Time bounds |
| Compute power and cost-aware thresholds | Error costs |
| Design offline backtest and online experiment | Label timing |
| Write SQL to flag coordinated fake accounts | Rule evidence |
Before submitting, test no history, exact boundaries, equal timestamps, late availability, repeated device use, missing devices, and a legitimate shared-device case. Explain the expected count before running each test. Continue with SQL interview practice once you can defend both halves of the answer: why the query flags an event, and why that flag is still not a fraud verdict.