Percentiles
SQL · Extra Concepts
Theory
A percentile is the value below which a given percentage of the data falls. The 50th percentile is the median: half of the salaries are below it and half above. The 90th percentile of delivery time tells you that 90% of orders arrived faster than that value. Percentiles describe a distribution far better than the average when data is skewed, because one huge salary pulls the average up but barely moves the median.
Standard SQL offers two ordered-set aggregates. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) is continuous: with an even number of rows it interpolates between the two middle values. PERCENTILE_DISC(0.5) returns an actual value from the data. PostgreSQL, Oracle and Snowflake support both as aggregates; SQL Server supports them as window functions with OVER; SQLite and MySQL lack them, so you sort the values and pick the middle one(s) with LIMIT/OFFSET, averaging the two middle values for an even count.
Why we use it
Averages are distorted by outliers and hide how values are spread. Percentiles show typical and extreme values (median, 90th, 99th) that describe salaries, response times and order sizes honestly.
When to use it
Use the median for 'typical' values in skewed data and high percentiles (p90, p95) for service levels and outliers. Use PERCENTILE_DISC when the answer must be a real value; use AVG only when data is roughly symmetric.
How it works
- Pick the percentile p, e.g. 0.5 for the median
- Sort the values: ORDER BY salary
- Find position p * (n - 1) in the sorted list
- Position between two rows? Yes: CONT interpolates; DISC picks a real value. No: Return that value.
- Percentile value for the group
Syntax
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY column)Example
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;Key points
- Postgres/Oracle/SQL Server support PERCENTILE_CONT natively
- The 50th percentile is the median
- PERCENTILE_CONT interpolates; PERCENTILE_DISC returns a real value from the data
- SQL Server uses them as window functions with OVER (...)
- The median resists outliers far better than AVG