Medians, percentiles and buckets

When a few big values skew the average, use the median, percentiles and NTILE buckets for a more honest picture.

The average deal is worth $40,929. But only 24 of the 70 deals are worth that much or more. A handful of $120,000 and $150,000 deals pull the average up, so it describes almost nobody. The median, the middle value, is $25,000, which is much closer to a typical deal.

Mean vs. median

When the mean is well above the median, a few large values are skewing it. Money, durations and counts of things are almost always skewed like this. Report the median, or both side by side.

Median in the sandbox

The sandbox’s SQLite build includes median(), used like any aggregate:

SELECT ROUND(AVG(amount)) AS mean_amount,
       median(amount)     AS median_amount
FROM opportunities;

It also has lower_quartile() and upper_quartile() for the 25th and 75th percentiles: $12,000 and $60,000 here. Half of all deals fall between those two values.

These are not standard SQL, and most databases spell them differently (see the dialect notes). The ideas carry over, the names don’t.

Median without a median function

On a database with no median function, number the sorted rows and take the middle one, or the average of the two middle ones when there’s an even count:

WITH ordered AS (
    SELECT amount,
           ROW_NUMBER() OVER (ORDER BY amount) AS rn,
           COUNT(*)     OVER ()                AS n
    FROM opportunities
)
SELECT AVG(amount) AS median_amount
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

With 70 deals, that keeps rows 35 and 36 and averages them: $25,000. With an odd count, both expressions point at the same middle row. The trick depends on whole-number division (lesson 10), which is one of the few times it helps.

Why this matters: skewed groups

Here’s the mean and median days to close for each lead source:

By the mean, Unknown looks like the slowest source. By the median, it’s mid-pack: a few very slow deals pull its average up. A report built on the mean would send someone chasing the wrong problem. The count matters too: Inbound’s 4 deals are too few to say much.

Buckets with NTILE

NTILE(4) splits sorted rows into four groups of nearly equal size, numbered 1 to 4:

WITH bucketed AS (
    SELECT amount,
           NTILE(4) OVER (ORDER BY amount) AS quartile
    FROM opportunities
)
SELECT quartile,
       COUNT(*)    AS deals,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM bucketed
GROUP BY quartile;

Use it to label deals small to large, or customers by spend, without picking cutoffs by hand. Note that $25,000 shows up in two buckets: NTILE keeps the groups even, even if that splits tied values.

Common mistakes

Dialect notes

PERCENTILE_CONT interpolates between values, so the median of 10 and 20 is 15. PERCENTILE_DISC returns an actual value from the data.

Snowflake notes

Snowflake has MEDIAN(x), MODE(x), and PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY x) as a regular aggregate, so it works with GROUP BY. APPROX_PERCENTILE(x, 0.9) is much faster on very large tables and usually within a fraction of a percent.

More in the field guide: Median, percentiles and mode and Percentiles & NTILE. Exercises A5 and M8 are the Snowflake versions.

Try it

For each lead source, show the number of closed deals and the mean and median whole days to close, both rounded to 1 decimal place, slowest median first. Deals with no lead source go under Unknown. Your result should match the table above.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson