Quantile Range Analysis

Measure the spread of the middle bulk of data to ignore extreme tails.

In this lesson, you will learn how to measure the spread of the middle bulk of your data to ignore the influence of extreme outliers.

Why Focus on the "Middle Bulk"?

While metrics like Standard Deviation describe how data spreads around an average, they are highly sensitive to extreme outliers—the very high or very low values that can skew your results. Quantile Range Analysis (often called the Interquartile Range, or IQR) solves this by focusing on the middle 50% of your data. By discarding the top 25% and bottom 25%, you get a robust view of where the "typical" values lie, free from the noise of anomalous spikes or crashes.

Calculating the Range in SQL

To calculate this, we use the PERCENTILE_CONT window function, which allows us to find the values at specific cutoff points (quantiles).

Imagine we are analyzing order_totals in a retail database:

SELECT 
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_total) OVER () - 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_total) OVER () AS iqr
FROM orders;

In this query:

PERCENTILE_CONT(0.75) finds the value below which 75% of your data falls.
PERCENTILE_CONT(0.25) finds the value below which 25% of your data falls.
Subtracting the two gives you the range of the middle 50% of your data.

When to Use This

This analysis is vital when your data is heavily skewed. For instance, in financial ledger data, a few massive transactions can make the average look misleadingly high. By calculating the Quantile Range, you can determine if your "normal" daily transactions are clustered tightly together or if they have a wide, unpredictable spread. Unlike the standard deviation, which gives higher weight to extreme values, the Quantile Range remains stable even if your extreme tails fluctuate wildly.

It provides a clean, reliable window into the behavior of your most representative data points, allowing for better-informed business logic without the distortion caused by outliers.

Key Takeaway: Quantile Range Analysis ignores the extreme tails of your distribution to provide a stable, robust measurement of the middle 50% of your data.