Median and percentiles in SQL

Use PERCENTILE_CONT(0.5), which gives the median. Where it exists, MEDIAN(x) is the short form. How you call it differs a lot between engines, which is the main thing to know.

Syntax by engine

-- Postgres, Snowflake, Databricks: ordered-set aggregate
SELECT city,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_value
FROM orders
GROUP BY city;

-- BigQuery: only a window (analytic) function, so it repeats on every row
SELECT DISTINCT city,
       PERCENTILE_CONT(order_value, 0.5) OVER (PARTITION BY city) AS median_value
FROM orders;

-- BigQuery, approximate and cheaper
SELECT city, APPROX_QUANTILES(order_value, 100)[OFFSET(50)] AS approx_median
FROM orders
GROUP BY city;

CONT vs DISC

For values 10, 20, 30, 40, the median is between 20 and 30.

• PERCENTILE_CONT interpolates, so it returns 25.
• PERCENTILE_DISC returns an actual value from the data, the first one at or above the percentile, so 20.

For even counts, CONT gives the "textbook" median. For things like "the median order id" or any non-numeric type, DISC is the only one that makes sense.

Without the function

Number the rows per city and count them, then average the one or two middle rows:

WITH r AS (
  SELECT city, order_value,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY order_value) AS rn,
         COUNT(*)     OVER (PARTITION BY city) AS cnt
  FROM orders
)
SELECT city, AVG(order_value) AS median_value
FROM r
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY city;

Cost

A median needs the values in order, so unlike SUM it cannot be built from small partial results per worker. On a billion rows it is heavy. If an estimate is fine, use the approximate version. If it is a dashboard number, compute it once into a summary table.

Mention NULLs: percentile functions ignore NULL values, so a median of a column with many NULLs describes only the rows that have a value.