SQL Quest › SQL Interview Questions › Subqueries & CTEs
3-Sigma Anomaly: Transaction Amount Outliers
The fraud analyst's first move: flag transactions with amount more than 3 standard deviations above the mean. Compute mean and stddev across all transactions, then return the outliers — these are the high-value fraud candidates worth investigating first. Show txn_id, account_id, amount, txn_at, z_score (rounded to 2 decimals). Order by z_score descending.
SQLite has no STDDEV — compute it yourself: SQRT(AVG((amount - mean)^2)).
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
transactions
| txn_id | account_id | amount | txn_at | merchant_id | lat | lng | status |
|---|---|---|---|---|---|---|---|
| 1 | 149 | 151.84 | 2026-03-04T12:39:39.078Z | 24 | 40.342 | -74.4092 | completed |
| 2 | 21 | 10.72 | 2026-03-04T13:04:50.641Z | 7 | 52.9366 | 13.3229 | completed |
| 3 | 35 | 158.78 | 2026-03-04T13:27:15.528Z | 25 | 52.7397 | 13.3134 | completed |
Expected output: High-value transaction outliers
Hint
Concepts
SELECT CTE Aggregation JOIN Statistics
Practise the topic: SQL practice questions · CTE practice · GROUP BY exercises · JOIN practice · Advanced SQL interview questions
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test