How does the outlier detection query work?

The z-score is how many standard deviations a value is from the mean; the query flags those beyond 3 in either direction. The interquartile version flags values more than 1.5 times the IQR outside the middle half of the data. A flagged value is a prompt to look, not proof of an error: large genuine orders also appear.

What commonly goes wrong with outlier detection in SQL?

The mean and standard deviation are themselves pulled by outliers, which can hide them; the IQR version is steadier.

Outlier Detection in SQL

Outlier detection flags values far from the typical range, using a z-score or the interquartile range, so unusual orders or readings can be reviewed.

Example query

This example assumes: orders(id bigint, customer_id bigint, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH stats AS (
  SELECT AVG(total_amount) AS mean_value,
         STDDEV_SAMP(total_amount) AS std_dev
  FROM orders
)
SELECT o.id,
       o.customer_id,
       o.total_amount,
       (o.total_amount - s.mean_value) / NULLIF(s.std_dev, 0) AS z_score
FROM orders o
CROSS JOIN stats s
WHERE ABS((o.total_amount - s.mean_value) / NULLIF(s.std_dev, 0)) > 3
ORDER BY z_score DESC;

Interquartile range version (less affected by the outliers themselves)

WITH q AS (
  SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS q1,
         PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS q3
  FROM orders
)
SELECT o.id, o.customer_id, o.total_amount
FROM orders o
CROSS JOIN q
WHERE o.total_amount < q.q1 - 1.5 * (q.q3 - q.q1)
   OR o.total_amount > q.q3 + 1.5 * (q.q3 - q.q1)
ORDER BY o.total_amount DESC;

How it works

The z-score is how many standard deviations a value is from the mean; the query flags those beyond 3 in either direction.
The interquartile version flags values more than 1.5 times the IQR outside the middle half of the data.
A flagged value is a prompt to look, not proof of an error: large genuine orders also appear.

MySQL, BigQuery, SQL Server and SQLite differences

STDDEV_SAMP is PostgreSQL, MySQL, BigQuery and Oracle (SQL Server calls it STDEV). PERCENTILE_CONT is not available in MySQL or SQLite.

Common mistakes

The mean and standard deviation are themselves pulled by outliers, which can hide them; the IQR version is steadier.
Skewed data such as order values flags many large but ordinary orders; compute the score within a segment.
Thresholds of 3 or 1.5 are conventions, not rules; choose them with the cost of a false alarm in mind.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Which orders are unusually large compared with the rest?
Flag daily sales figures that are far outside the normal range.