SQL percentiles and median: PERCENTILE_CONT and NTILE

#Why it matters

Averages lie on skewed data, and almost all business data is skewed: order values, delivery times, salaries, response times. The SQL median and percentile functions give you the numbers that hold up in a meeting: "half of orders arrive within 10 days, 9 in 10 within 21". Statistics appears in 58% of Data Analyst and 82% of Data Scientist job descriptions we studied, and SQL in 88% of Data Analyst ones, so "write a median query" and "which customers are in the top 10%" are standard interview tasks. This lesson covers the functions, the dialect differences, and the query for databases that have no percentile function at all.

#The concept

#Mean vs median vs percentile

• The mean adds everything and divides. One 200-day delivery pulls it up for everyone.

• The median (the 50th percentile, p50) is the middle value. Half the rows are below it.

• The p90 is the value 90% of rows are at or below. Service teams promise p90 or p95, not the mean, because it describes what most customers actually experience, including the slow tail.

When the mean is well above the median, the data has a long right tail. Report the median as the headline and a high percentile for the tail. The Mean vs median: central tendency and outliers lesson covers the statistics side.

#PERCENTILE_CONT vs PERCENTILE_DISC

For an even number of values there is no single middle row:

• PERCENTILE_CONT interpolates between the two neighbours. The median of 10, 20, 30, 40 is 25, a value that is not in the data.

• PERCENTILE_DISC returns an actual value from the data: the first one whose cumulative share reaches the percentile, so 20.

Use CONT for measurements (days, rupees). Use DISC when the answer must be a real row, such as a real price point or a real customer.

#The syntax in each database

Two details catch people. In SQL Server, PERCENTILE_CONT exists only as a window function (... OVER (PARTITION BY ...)), so you take DISTINCT or MAX of it to get one row per group. PostgreSQL has no MEDIAN() at all.

#No percentile function: the ROW_NUMBER and COUNT method

Number the rows in order, count them, and average the middle one or two:

WITH ordered AS (
    SELECT x,
           ROW_NUMBER() OVER (ORDER BY x) AS rn,
           COUNT(*)     OVER ()           AS n
    FROM t
)
SELECT AVG(x) AS median
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);   -- integer division

For odd n both expressions give the same row; for even n they give the two middle rows and AVG interpolates. / is integer division in PostgreSQL and SQL Server on integers; MySQL needs DIV and DuckDB //.

#NTILE: buckets by count

NTILE(10) OVER (ORDER BY revenue DESC) splits rows into 10 groups with as equal a number of rows as possible: deciles. When rows do not divide evenly, the first buckets get one extra row. NTILE splits by count, not by value, so tied values can land in different buckets; add a tie-breaker to make it repeatable.

#PERCENT_RANK and CUME_DIST: where does this row sit?

• PERCENT_RANK() = (rank - 1) / (rows - 1). The lowest row is 0, the highest is 1.

• CUME_DIST() = share of rows with a value less than or equal to this one. Never 0.

Use them to tag each customer "top 8% by spend" without picking bucket edges.

#Approximate percentiles

Exact percentiles need every value sorted in one place. Warehouses offer approximate versions (APPROX_QUANTILES in BigQuery, APPROX_PERCENTILE in Snowflake and Databricks, approx_quantile in DuckDB) built on small sketches that can be merged across machines. You accept a small error, usually larger in the extreme tail, for less memory and the ability to combine pre-aggregated results. Fine for dashboards; use exact values for billing or SLAs that carry penalties.

#Worked example: delivery times and order values in Brazil

We use the Olist e-commerce dataset (about 100,000 real orders from a Brazilian marketplace, 2016 to 2018, licensed CC BY-NC-SA 4.0) for delivery times and order values, and the UCI Online Retail data (CC BY 4.0) for customer deciles. Queries run in DuckDB unless marked PostgreSQL 16. Every output is from a real run.

import duckdb

con = duckdb.connect()
con.sql("CREATE TABLE orders AS SELECT * FROM read_csv('olist_orders_dataset.csv', header = true)")
con.sql("CREATE TABLE customers AS SELECT * FROM read_csv('olist_customers_dataset.csv', header = true)")
con.sql("CREATE TABLE payments AS SELECT * FROM read_csv('olist_order_payments_dataset.csv', header = true)")
con.sql("""
    CREATE TABLE retail AS
    SELECT * FROM read_csv('online-retail-dataset.csv', header = true,
                           timestampformat = '%m/%d/%Y %H:%M')
""")
con.sql("CREATE TABLE sales AS SELECT * FROM retail WHERE Quantity > 0 AND UnitPrice > 0")

#Step 1: one row per delivered order

CREATE OR REPLACE TABLE delivered AS
SELECT o.order_id, c.customer_unique_id, c.customer_state AS state,
       p.order_value,
       ROUND(date_diff('minute', o.order_purchase_timestamp,
                       o.order_delivered_customer_date) / 1440.0, 2) AS delivery_days
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN (SELECT order_id, SUM(payment_value) AS order_value
      FROM payments GROUP BY order_id) AS p
  ON p.order_id = o.order_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL;

SELECT COUNT(*) AS orders,
       ROUND(AVG(order_value), 2) AS mean_value,
       ROUND(MEDIAN(order_value), 2) AS median_value,
       ROUND(AVG(delivery_days), 2) AS mean_days,
       MEDIAN(delivery_days) AS median_days,
       MAX(delivery_days) AS max_days
FROM delivered;

┌────────┬────────────┬──────────────┬───────────┬─────────────┬──────────┐
│ orders │ mean_value │ median_value │ mean_days │ median_days │ max_days │
│ int64  │   double   │    double    │  double   │   double    │  double  │
├────────┼────────────┼──────────────┼───────────┼─────────────┼──────────┤
│  96469 │     159.86 │       105.28 │     12.56 │       10.22 │   209.63 │
└────────┴────────────┴──────────────┴───────────┴─────────────┴──────────┘

Payments are summed per order in a subquery first, so orders paid in instalments or with vouchers are not counted twice (the fan-out problem from the joins module). The mean order is R$159.86butthemedianisR$105.28: the mean sits 52% above what a typical order is worth, pulled up by a minority of large baskets. Delivery is the same story: a 209-day outlier drags the mean to 12.56 days while the typical order takes 10.22.

#Step 2: PERCENTILE_CONT vs PERCENTILE_DISC

SELECT quantile_cont(x, 0.5) AS percentile_cont,
       quantile_disc(x, 0.5) AS percentile_disc,
       median(x) AS median
FROM (VALUES (10), (20), (30), (40)) AS t(x);

┌─────────────────┬─────────────────┬────────┐
│ percentile_cont │ percentile_disc │ median │
│     double      │      int32      │ double │
├─────────────────┼─────────────────┼────────┤
│            25.0 │              20 │   25.0 │
└─────────────────┴─────────────────┴────────┘

CONT interpolates to 25, a value not in the data. DISC returns 20, a real value, and keeps the column's integer type. DuckDB's median() is CONT.

#Step 3: p50, p90 and p99 per state

SELECT state, COUNT(*) AS orders,
       ROUND(AVG(delivery_days), 1) AS mean_days,
       ROUND(quantile_cont(delivery_days, 0.5), 1) AS p50,
       ROUND(quantile_cont(delivery_days, 0.9), 1) AS p90,
       ROUND(quantile_cont(delivery_days, 0.99), 1) AS p99
FROM delivered
GROUP BY state
HAVING COUNT(*) >= 1000
ORDER BY p90 DESC;

┌─────────┬────────┬───────────┬────────┬────────┬────────┐
│  state  │ orders │ mean_days │  p50   │  p90   │  p99   │
│ varchar │ int64  │  double   │ double │ double │ double │
├─────────┼────────┼───────────┼────────┼────────┼────────┤
│ CE      │   1279 │      21.3 │   18.2 │   34.7 │   71.4 │
│ BA      │   3256 │      19.3 │   16.9 │   31.8 │   56.6 │
│ PE      │   1593 │      18.4 │   15.7 │   30.9 │   52.9 │
│ RJ      │  12350 │      15.3 │   12.0 │   29.1 │   57.3 │
│ RS      │   5344 │      15.3 │   13.2 │   25.9 │   45.6 │
│ SC      │   3546 │      15.0 │   13.0 │   25.8 │   43.4 │
│ ES      │   1995 │      15.8 │   13.6 │   25.4 │   46.0 │
│ GO      │   1957 │      15.6 │   14.0 │   24.8 │   44.5 │
│ DF      │   2080 │      13.0 │   11.4 │   21.6 │   37.1 │
│ MG      │  11354 │      12.0 │   10.3 │   20.2 │   36.7 │
│ PR      │   4923 │      12.0 │   10.4 │   20.1 │   37.7 │
│ SP      │  40493 │       8.8 │    7.2 │   15.8 │   32.2 │
└─────────┴────────┴───────────┴────────┴────────┴────────┘
  12 rows                                       6 columns

This is a report a logistics manager can act on. A customer in São Paulo (SP), where most sellers are, gets half their orders within 7.2 days and 9 in 10 within 15.8. In Ceará (CE) in the north-east, the median is 18.2 days and the slowest 1% wait over 71. Look at Rio de Janeiro (RJ): its median (12.0) is close to Paraná's (10.4), but its p90 (29.1) is much worse. The median alone would hide that RJ has a long tail of very late deliveries. The HAVING COUNT(*) >= 1000 matters too: a p99 from 41 orders is one order.

#Step 4: the same in PostgreSQL

SELECT state, count(*) AS orders,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY delivery_days) AS p50_cont,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY delivery_days) AS p50_disc,
       percentile_cont(ARRAY[0.9, 0.99]) WITHIN GROUP (ORDER BY delivery_days) AS p90_p99
FROM delivered
WHERE state IN ('AC', 'RR', 'SC', 'SP')
GROUP BY state
ORDER BY state;

SELECT median(delivery_days) FROM delivered;

 state | orders |      p50_cont      | p50_disc |                p90_p99
-------+--------+--------------------+----------+---------------------------------------
 AC    |     80 | 18.354999999999997 |    18.33 | {31.08300000000001,68.05249999999997}
 RR    |     41 |              25.01 |    25.01 | {53.19,132.85600000000014}
 SC    |   3546 |             13.015 |    13.01 | {25.795,43.426500000000075}
 SP    |  40493 |               7.21 |     7.21 | {15.77,32.18160000000003}
 
ERROR:  function median(numeric) does not exist
LINE 1: SELECT median(delivery_days) FROM delivered;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Four things to read here:

• Even counts differ, odd counts agree. AC (80 orders) and SC (3,546) have two middle values, so CONT interpolates (18.355) while DISC picks the lower one (18.33). RR (41) and SP (40,493) have one middle value and both agree.

• CONT returns double precision, hence 18.354999999999997. Round it, or cast to numeric, before it reaches a report.

• One call can return several percentiles by passing an array.

• median() does not exist in PostgreSQL.

#Step 5: a median without any percentile function

What you write in MySQL, or in an interview that says "no built-ins". In PostgreSQL:

WITH ordered AS (
    SELECT state, delivery_days,
           ROW_NUMBER() OVER (PARTITION BY state ORDER BY delivery_days) AS rn,
           COUNT(*)     OVER (PARTITION BY state) AS n
    FROM delivered
    WHERE state IN ('AC', 'RR', 'SC', 'SP')
)
SELECT state, n, AVG(delivery_days) AS median_days
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY state, n
ORDER BY state;

 state |   n   |     median_days
-------+-------+---------------------
 AC    |    80 | 18.3550000000000000
 RR    |    41 | 25.0100000000000000
 SC    |  3546 | 13.0150000000000000
 SP    | 40493 |  7.2100000000000000

Identical to percentile_cont in Step 4 for all four states, even and odd. For n = 80 the rows kept are 40 and 41 and their average is the interpolated median; for n = 41 both expressions give row 21. Adding PARTITION BY state to both window functions gives a median per group.

#Step 6: customer deciles with NTILE

Back to the UK gift shop. Which share of revenue comes from the top 10% of customers?

CREATE OR REPLACE TABLE customer_revenue AS
SELECT CustomerID, SUM(Quantity * UnitPrice) AS revenue
FROM sales
WHERE CustomerID IS NOT NULL
GROUP BY CustomerID;

WITH d AS (
    SELECT CustomerID, revenue,
           NTILE(10) OVER (ORDER BY revenue DESC, CustomerID) AS decile
    FROM customer_revenue
)
SELECT decile, COUNT(*) AS customers,
       ROUND(MIN(revenue), 2) AS min_revenue,
       ROUND(SUM(revenue), 2) AS revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1) AS pct_of_total
FROM d
GROUP BY decile
ORDER BY decile;

┌────────┬───────────┬─────────────┬────────────┬──────────────┐
│ decile │ customers │ min_revenue │  revenue   │ pct_of_total │
│ int64  │   int64   │   double    │   double   │    double    │
├────────┼───────────┼─────────────┼────────────┼──────────────┤
│      1 │       434 │      3649.1 │ 5469382.46 │         61.4 │
│      2 │       434 │     2059.45 │  1180055.0 │         13.2 │
│      3 │       434 │     1353.74 │  728457.38 │          8.2 │
│      4 │       434 │      942.26 │  491753.71 │          5.5 │
│      5 │       434 │      674.45 │  344466.86 │          3.9 │
│      6 │       434 │       489.6 │  252716.63 │          2.8 │
│      7 │       434 │      351.65 │   179720.0 │          2.0 │
│      8 │       434 │      250.04 │  132016.26 │          1.5 │
│      9 │       433 │      156.58 │   86895.44 │          1.0 │
│     10 │       433 │        3.75 │   45944.16 │          0.5 │
└────────┴───────────┴─────────────┴────────────┴──────────────┘
  10 rows                                            5 columns

• The top decile brings 61.4% of revenue. That one number shapes retention budgets, account management and how much a lost big customer hurts.

• Uneven buckets: 4,338 customers do not divide by 10, so deciles 1 to 8 have 434 and 9 to 10 have 433.

• min_revenue gives the decile edges: you are in the top 10% from 3,649.10.

• SUM(SUM(revenue)) OVER () is a window over an aggregate: the grand total next to each decile, for the percentage.

• CustomerID in the ORDER BY makes bucket edges repeatable if two customers have the same revenue.

#Step 7: PERCENT_RANK and CUME_DIST for single customers

WITH r AS (
    SELECT CustomerID, ROUND(revenue, 2) AS revenue,
           ROUND(PERCENT_RANK() OVER (ORDER BY revenue), 4) AS percent_rank,
           ROUND(CUME_DIST()    OVER (ORDER BY revenue), 4) AS cume_dist
    FROM customer_revenue
)
SELECT * FROM r WHERE CustomerID IN (12347, 12348, 12350, 14646) ORDER BY revenue;

┌────────────┬───────────┬──────────────┬───────────┐
│ CustomerID │  revenue  │ percent_rank │ cume_dist │
│   int64    │  double   │    double    │  double   │
├────────────┼───────────┼──────────────┼───────────┤
│      12350 │     334.4 │       0.2831 │    0.2833 │
│      12348 │   1797.24 │       0.7683 │    0.7683 │
│      12347 │    4310.0 │       0.9228 │    0.9228 │
│      14646 │ 280206.02 │          1.0 │       1.0 │
└────────────┴───────────┴──────────────┴───────────┘

Customer 12347 spent more than about 92% of customers, so a CRM could tag them "top 8%". As in Step 6, the window ran over all 4,338 customers before the outer WHERE picked four.

#Step 8: approximate vs exact

SELECT ROUND(quantile_cont(order_value, 0.9), 2)   AS exact_p90,
       ROUND(approx_quantile(order_value, 0.9), 2) AS approx_p90,
       ROUND(quantile_cont(order_value, 0.99), 2)  AS exact_p99,
       ROUND(approx_quantile(order_value, 0.99), 2) AS approx_p99
FROM delivered;

┌───────────┬────────────┬───────────┬────────────┐
│ exact_p90 │ approx_p90 │ exact_p99 │ approx_p99 │
│  double   │   double   │  double   │   double   │
├───────────┼────────────┼───────────┼────────────┤
│    305.97 │     304.65 │    1052.4 │    1072.87 │
└───────────┴────────────┴───────────┴────────────┘

The approximate p90 is off by 0.4%, the p99 by about 2%: the further into the tail, the fewer values the sketch has to work with. Good enough for a trend chart; not for a refund promise that says "99% of orders within X".

#Common mistakes

• Reporting the mean of skewed data as "typical". Lead with the median and a high percentile; give the mean only next to them.

• Percentiles on tiny groups. A p99 from 41 orders is the single slowest order. Set a minimum group size with HAVING.

• Mixing up CONT and DISC. CONT invents in-between values; DISC returns a real one. Pick on purpose and say which.

• Expecting MEDIAN() in PostgreSQL or MySQL. Use PERCENTILE_CONT ... WITHIN GROUP or the ROW_NUMBER method.

• Forgetting SQL Server's PERCENTILE_CONT is a window function. It returns one value per row; wrap with DISTINCT or an aggregate for one row per group.

• Averaging percentiles. The average of daily p90s is not the monthly p90. Recompute from raw rows (or merge approximate sketches where the warehouse supports it).

• NTILE without a tie-breaker. Equal values can switch buckets between runs.

• Joining before aggregating. Summing payments after joining to items multiplies values. Aggregate each child table to the order level first.

#Interview angle

#Practice task

1. For Olist states with at least 1,000 delivered orders, compute the mean, median and p90 order value, plus the mean-to-median ratio. Show the five states with the highest median.

2. Prove your no-function median: compute the median order value over all delivered orders with the ROW_NUMBER method and compare it with MEDIAN().

3. Split the Online Retail customers into quartiles by revenue with NTILE(4) and show each quartile's size, revenue range and share of revenue.

Done when your hand-made median matches the built-in one to the cent, and you can say in one sentence what share of revenue the top quarter of customers brings.

SELECT state, COUNT(*) AS orders,
       ROUND(AVG(order_value), 2) AS mean_value,
       ROUND(MEDIAN(order_value), 2) AS median_value,
       ROUND(quantile_cont(order_value, 0.9), 2) AS p90_value,
       ROUND(AVG(order_value) / MEDIAN(order_value), 2) AS mean_to_median
FROM delivered
GROUP BY state
HAVING COUNT(*) >= 1000
ORDER BY median_value DESC
LIMIT 5;

┌─────────┬────────┬────────────┬──────────────┬───────────┬────────────────┐
│  state  │ orders │ mean_value │ median_value │ p90_value │ mean_to_median │
│ varchar │ int64  │   double   │    double    │  double   │     double     │
├─────────┼────────┼────────────┼──────────────┼───────────┼────────────────┤
│ PE      │   1593 │     194.02 │        134.0 │    378.79 │           1.45 │
│ CE      │   1279 │     208.34 │       130.13 │    398.51 │            1.6 │
│ BA      │   3256 │     181.59 │       118.05 │    336.01 │           1.54 │
│ GO      │   1957 │     170.82 │       113.38 │    332.78 │           1.51 │
│ RJ      │  12350 │     166.45 │       112.38 │    308.96 │           1.48 │
└─────────┴────────┴────────────┴──────────────┴───────────┴────────────────┘

WITH ordered AS (
    SELECT order_value,
           ROW_NUMBER() OVER (ORDER BY order_value) AS rn,
           COUNT(*) OVER () AS n
    FROM delivered
)
SELECT MAX(n) AS n, AVG(order_value) AS median_without_function,
       (SELECT MEDIAN(order_value) FROM delivered) AS median_function
FROM ordered
WHERE rn IN ((n + 1) // 2, (n + 2) // 2);

┌───────┬─────────────────────────┬─────────────────┐
│   n   │ median_without_function │ median_function │
│ int64 │         double          │     double      │
├───────┼─────────────────────────┼─────────────────┤
│ 96469 │                  105.28 │          105.28 │
└───────┴─────────────────────────┴─────────────────┘

WITH q AS (
    SELECT CustomerID, revenue,
           NTILE(4) OVER (ORDER BY revenue DESC, CustomerID) AS quartile
    FROM customer_revenue
)
SELECT quartile, COUNT(*) AS customers,
       ROUND(MIN(revenue), 2) AS min_revenue,
       ROUND(MAX(revenue), 2) AS max_revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1) AS pct_of_revenue
FROM q
GROUP BY quartile
ORDER BY quartile;

┌──────────┬───────────┬─────────────┬─────────────┬────────────────┐
│ quartile │ customers │ min_revenue │ max_revenue │ pct_of_revenue │
│  int64   │   int64   │   double    │   double    │     double     │
├──────────┼───────────┼─────────────┼─────────────┼────────────────┤
│        1 │      1085 │     1661.84 │   280206.02 │           79.1 │
│        2 │      1085 │      674.45 │     1661.44 │           13.0 │
│        3 │      1084 │       307.4 │      673.26 │            5.6 │
│        4 │      1084 │        3.75 │      307.09 │            2.2 │
└──────────┴───────────┴─────────────┴─────────────┴────────────────┘

#Further reading

• PostgreSQL: ordered-set aggregate functions: percentile_cont, percentile_disc and mode.

• DuckDB: aggregate functions: median, quantile_cont, quantile_disc and approx_quantile.

• SQL Server: PERCENTILE_CONT: the window-function form.

• BigQuery: APPROX_QUANTILES: approximate percentiles at warehouse scale.

• Olist dataset: source and CC BY-NC-SA 4.0 licence of the orders data.