Level 7 · Flag outliers, don't delete them

An outlier is a value far from the rest. Some are errors (a quantity of -16), some are real and important (a business customer furnishing an office). Deleting both kinds hides information, so the standard practice is to flag first and decide later.

Rule-based flags

Some values are impossible by definition. A sold quantity of zero or less is a data-entry error. In this dataset 24 order lines have one: 8 zeros and 16 negatives, from -1 down to -16. A rule flag is a plain condition:

CASE WHEN quantity <= 0 THEN 1 ELSE 0 END AS bad_line

Keep those lines out of revenue, but count them. 19 completed orders contain one.

Statistical flags: the IQR fence

For values that are possible but unusual, compare them to the distribution. The interquartile range (IQR) is Q3 minus Q1, the spread of the middle half of the data. Tukey’s rule flags anything above Q3 + 1.5 × IQR (and below Q1 − 1.5 × IQR, which is under zero for order values).

SELECT
  quantile_cont(order_value, 0.25) AS q1,
  quantile_cont(order_value, 0.75) AS q3
FROM order_values;

For the 10,849 completed orders, Q1 is $93.99 and Q3 is $659.94, so the fence sits at about $1,508.87. The median order is $268.96 and the largest is $11,103.27. quantile_cont interpolates between values; quantile_disc returns an actual value from the data.

Reading the result

926 completed orders, about 8.5%, are above the fence. That is a lot for an “outlier” rule, and it tells you something: order values are heavily right-skewed, with many small orders and a long tail of big furniture and business purchases. The IQR rule was designed for roughly symmetric data. Those orders are real revenue, which is exactly why you flag them rather than delete them. Finance might forecast them separately or model the logarithm of order value instead.

Common mistakes

• Deleting instead of flagging. You can’t undo a WHERE you never told anyone about.
• Recomputing the fence after removing outliers. The fence moves every time; compute it once on the full data.
• A fence per group by accident. Decide whether the threshold is global or per channel, and make the query say so.
• Mean and standard deviation on skewed data. The big orders inflate both, so a “3 sigma” rule misses what it should catch. Quartiles resist that.

In SQL Server

There is no quantile_cont. PERCENTILE_CONT is a window function there, so it repeats the value on every row:

SELECT DISTINCT
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_value) OVER () AS q1,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_value) OVER () AS q3
FROM order_values;

Postgres and Snowflake use percentile_cont(0.75) WITHIN GROUP (ORDER BY order_value) as an aggregate.