Percentiles and median

Ordered-set aggregates that return the median or any percentile of a column.

Syntax

percentile_cont(fraction) WITHIN GROUP (ORDER BY expr)    -- interpolated
percentile_disc(fraction) WITHIN GROUP (ORDER BY expr)    -- an actual value
percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY expr)
mode() WITHIN GROUP (ORDER BY expr)
ntile(n) OVER (ORDER BY expr) / percent_rank() / cume_dist()    -- per-row

percentile_cont(0.5) is the median: it interpolates between the two middle values when the count is even. It returns double precision (or interval for intervals).
percentile_disc(0.5) returns the first actual value whose cumulative share reaches 0.5 — always an existing value, same type as the input.
Pass an array of fractions to get several percentiles at once (returns an array).
mode() returns the most frequent value.
NULL: NULLs are ignored.
They are aggregates, usable with GROUP BY, but in PostgreSQL they cannot be used as window functions (OVER). For per-row positions use percent_rank(), cume_dist() or ntile(n).
The median is much more robust than the average when data is skewed (salaries, order values).

Examples

Median vs average people

SELECT round(avg(base_salary), 2) AS avg_salary,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY base_salary) AS median_salary,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY base_salary) AS median_actual_value
FROM salary_history;

A mean above the median signals a right-skewed distribution.

Quartiles per group saas

SELECT priority,
       percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (
         ORDER BY extract(epoch FROM closed_at - opened_at) / 3600) AS hours_q1_q2_q3
FROM support_tickets
WHERE closed_at IS NOT NULL
GROUP BY priority
ORDER BY priority;

One array of three quartiles per priority.

Most common value shop

SELECT mode() WITHIN GROUP (ORDER BY method) AS most_used_method,
       count(*) AS payments
FROM payments
WHERE status = 'captured';

mode() picks the most frequent payment method.

Pitfalls

Writing percentile_cont(0.5) OVER (PARTITION BY ...): not supported in PostgreSQL; aggregate with GROUP BY and join back.
Expecting percentile_disc to interpolate (it returns an existing value).
Rounding a percentile_cont result with round(x, 2) without casting to numeric first (it is double precision).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: PERCENTILE_CONT/DISC exist only as window functions: ... WITHIN GROUP (ORDER BY x) OVER (PARTITION BY ...).
Oracle: PERCENTILE_CONT/DISC as aggregate or analytic; also MEDIAN(x).
Snowflake: MEDIAN(x), PERCENTILE_CONT/DISC ... WITHIN GROUP, APPROX_PERCENTILE.
BigQuery: PERCENTILE_CONT(x, 0.5) OVER () is analytic only; APPROX_QUANTILES(x, 100)[OFFSET(50)] as an aggregate.
MySQL: No percentile or median function; compute with ROW_NUMBER and COUNT.