Outlier Trimming Techniques

Outlier Trimming Techniques

In this lesson, you will learn how to identify and remove extreme noise from your datasets to ensure your analytical aggregates reflect the true behavior of your data.

The Problem with Extreme Values

When calculating averages or trends, a few extreme values—known as outliers—can significantly skew your results. For example, if you are analyzing the average purchase price of customers, a single "test transaction" for $1,000,000 can make your average look artificially high. Before performing heavy aggregation, we need to "trim" these values to improve the accuracy of our insights.

Common Trimming Approaches

While there are many statistical methods, the two most common approaches in SQL are Percentile Clipping and Z-Score Filtering.

1. Percentile Clipping: This involves removing the top and bottom $N$ percent of your data. This is useful when you know your data has extreme spikes at both ends.
2. Standard Deviation Filtering: Using the STDDEV we covered in lesson 18, we can exclude any values that fall beyond a specific number of standard deviations from the mean (e.g., beyond 3 standard deviations).

Implementing Trimming with a CTE

Using a Common Table Expression (CTE), we can first identify our bounds and then exclude the noise. Here is how you might filter out values beyond the 95th percentile:

WITH Bounds AS (
    SELECT 
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY purchase_amount) as upper_bound
    FROM sales
)
SELECT AVG(s.purchase_amount) as clean_average
FROM sales s, Bounds b
WHERE s.purchase_amount <= b.upper_bound;

In this snippet, we calculate the 95th percentile and use it as a gate to discard the top 5% of our transaction data. By doing this, the resulting AVG is no longer pulled upward by those extreme, noisy outliers. You can apply similar logic to remove the bottom 5% by calculating the 5th percentile, ensuring your dataset is tightly focused on the "normal" behavior you intend to measure.

Key Takeaway: Trimming outliers preserves the integrity of your aggregates by ensuring that extreme, unrepresentative data points do not distort your analytical conclusions.