Cheatsheet

SQL Distributions Cheatsheet

Median, percentiles, spread, mode, histograms, and outliers.

Which number to report

Question	DuckDB	PostgreSQL
Typical value, skewed data	`median(x)`	`percentile_cont(0.5) within group (order by x)`
The slow end	`quantile_cont(x, 0.9)`	`percentile_cont(0.9) within group (order by x)`
A percentile that really occurs	`quantile_disc(x, 0.9)`	`percentile_disc(0.9) within group (order by x)`
How far values spread	`stddev_samp(x)`	`stddev_samp(x)`
Most frequent value	`mode(x)`	`mode() within group (order by x)`
Do two columns move together	`corr(y, x)`	`corr(y, x)`

DuckDB accepts every PostgreSQL spelling in the table too. MySQL has the standard deviation functions but no median or percentile aggregate.

Middle and spread per group

Five-number summary per category

select   c.category_name,
         min(p.price)                       as min_price,
         quantile_cont(p.price, 0.25)       as p25,
         median(p.price)                    as median_price,
         quantile_cont(p.price, 0.75)       as p75,
         max(p.price)                       as max_price,
         round(stddev_samp(p.price), 2)     as price_stddev
from     products as p
join     categories as c on c.category_id = p.category_id
group by c.category_name
order by c.category_name

Most common value, ties handled

Top status, with its count

select   status,
         count(*) as orders
from     orders
group by status
order by orders desc, status
limit    1

mode(status) returns the same answer without the count, and picks an unspecified winner on a tie. Per group, rank the counts with row_number() (one winner) or rank() (every tied winner).

Share of each value

Percent of orders per status

select   status,
         count(*) as orders,
         round(100.0 * count(*) / sum(count(*)) over (), 1) as pct_of_orders
from     orders
group by status
order by orders desc, status

Histogram bands

Need	Expression
Equal-width band start	`floor(x / 10) * 10`
Band end	`floor(x / 10) * 10 + 10`
Named or uneven bands	`case when x < 10 then 'low' … end`
Four equal-count groups	`ntile(4) over (order by x)`
All counts in one cell (DuckDB)	`histogram(floor(x / 10) * 10)`

Keep the empty bands

select    bands.band_start,
          count(p.product_id) as products
from      range(0, 34, 2) as bands(band_start)
left join products as p
       on floor(p.price / 2) * 2 = bands.band_start
group by  bands.band_start
order by  bands.band_start

Count a column from the joined table, not *, or every empty band counts as 1. PostgreSQL's generate_series(0, 32, 2) plays the part of range, which stops before its end value.

Outliers

Outside 1.5 interquartile ranges

with fences as (
  select quantile_cont(price, 0.25) as p25,
         quantile_cont(price, 0.75) as p75
  from products
)
select p.product_name, p.price
from products as p
cross join fences as f
where p.price < f.p25 - 1.5 * (f.p75 - f.p25)
   or p.price > f.p75 + 1.5 * (f.p75 - f.p25)

The fence test returns nothing for Garden Shop: its most expensive product, $32, sits inside the upper fence of about $33.44. A z-score above 2 ((x - avg(x) over ()) / stddev_samp(x) over ()) is the other common rule; it flags that $32 product at 2.35.

Watch out

A median or standard deviation taken over a summary table's per-group averages goes wrong the same way an average of averages does. Compute from the raw rows. See averaging averages.

Lesson

SQL Median and Percentiles: MEDIAN and QUANTILE_CONT

The middle value and the slow end, which an average blurs.

Reference

SQL Statistical Aggregate Functions

Median, percentiles, standard deviation, mode, and histograms.

Mistake

Why Averaging Averages Gives the Wrong Result

Recompute from totals instead of averaging grouped averages.

Lesson

SQL Standard Deviation and Variance: STDDEV_SAMP

Measure how far values spread around their average.