Calculating Percentiles and Median in SQL

When analyzing data, averages can be misleading. A few outliers can skew the mean dramatically. That's why professionals rely on median and percentiles for more robust insights.

But SQL doesn't have a built-in MEDIAN() function in most databases. Let's explore how to calculate these statistics anyway.

Why Median Matters

Consider employee salaries:

• Mean salary: $100,000
• But if the CEO makes $1,000,000 and everyone else makes $50,000, the mean is misleading!
• Median salary: $50,000 (the "middle" value) tells the real story.

Method 1: Using PERCENT_RANK for Percentiles

The PERCENT_RANK() function assigns a percentile rank (0 to 1) to each row:

A percentile of 50 means half the data is below that value.

Method 2: Finding the Median with Window Functions

The median is the value at the 50th percentile. Here's how to find it:

How This Works

1. ROW_NUMBER() assigns position 1 to N
2. COUNT(*) OVER () gets the total count
3. For odd counts: (N+1)/2 gives the middle position
4. For even counts: Average of positions N/2 and N/2+1

Method 3: NTILE for Quartiles

NTILE(4) divides data into 4 equal groups (quartiles):

You can use NTILE(10) for deciles or NTILE(100) for fine-grained percentiles.

Method 4: Percentile Boundaries

Find the value at a specific percentile (e.g., 90th percentile):

Calculating Multiple Percentiles at Once

Need P25, P50 (median), and P75? Use conditional aggregation:

Group-Level Percentiles

Calculate median salary per department:

SELECT 
  department,
  AVG(salary) as median_salary
FROM (
  SELECT 
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department 
      ORDER BY salary
    ) as rn,
    COUNT(*) OVER (PARTITION BY department) as dept_count
  FROM employees
)
WHERE rn IN ((dept_count + 1) / 2, (dept_count + 2) / 2)
GROUP BY department;

Comparison: Mean vs Median

Notice how the outlier ($500,000) dramatically affects the mean but not the median!

Quick Reference

Statistic	SQL Approach
Percentile rank	PERCENT_RANK() OVER (ORDER BY col)
Median	ROW_NUMBER() + middle position formula
Quartiles (4 groups)	NTILE(4) OVER (ORDER BY col)
Deciles (10 groups)	NTILE(10) OVER (ORDER BY col)
Specific percentile	NTILE(100) + filter

Best Practices

1. Use median for skewed data: Income, prices, and response times often have outliers.

2. Report both mean and median: If they differ significantly, your data is skewed.

3. Consider NTILE limitations: With small datasets, NTILE groups may be uneven.

4. Index the ORDER BY column: Percentile calculations sort data, so indexes help.

Conclusion

While SQL lacks a native MEDIAN() in most databases, window functions provide powerful alternatives:

• PERCENT_RANK() for percentile ranks
• NTILE() for dividing into groups
• ROW_NUMBER() with arithmetic for exact median

These techniques give you robust statistical insights that go beyond simple averages.