Calculating Percentiles and Medians

Compute statistical distribution metrics to identify outliers in large datasets.

In this lesson, you will learn how to compute statistical distribution metrics—specifically percentiles and medians—to pinpoint outliers within massive analytical datasets.

Understanding Percentiles and Medians

While averages (means) are useful, they are notoriously sensitive to extreme values (outliers). If one customer spends $1,000,000 while everyone else spends$ 10, it skews the average to look like a high-value customer base. Percentiles help us understand the distribution of data: the median (50th percentile) is the middle value, while the 90th percentile represents the threshold where only 10% of your data points are higher. Identifying where these thresholds lie is critical for detecting performance bottlenecks or anomalies in large-scale event logs.

Implementation with Window Functions

In modern analytical SQL, we typically avoid self-joins or slow subqueries to calculate these metrics. Instead, we use PERCENTILE_CONT (continuous) or PERCENTILE_DISC (discrete) as window functions.

Consider an analytical table tracking request_latency across millions of transactions:

SELECT 
    request_id,
    request_latency,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY request_latency) 
        OVER () AS median_latency,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY request_latency) 
        OVER () AS p95_threshold
FROM server_logs;

Identifying Outliers

By calculating the 95th or 99th percentile, you establish a baseline for "normal" performance. Anything significantly above this threshold can be flagged as an outlier. If your query filters the table first (e.g., by region or service_type), the OVER() clause ensures the percentile is calculated relative to that specific group, allowing for granular anomaly detection.

Note: In massive datasets, these functions can be resource-intensive. Because they require sorting all data within the window frame, ensure your underlying data is effectively partitioned or clustered by the dimension you are grouping by to keep the calculation performant.

Key Takeaway: Percentiles and medians provide a robust alternative to averages by revealing the true shape of your data distribution, allowing you to mathematically define and isolate outliers.