Medians and Percentiles in SQL

"How long does a typical session last?" sounds like a job for avg(). But one visitor who leaves a tab open for ten minutes moves the average for everyone. The median sorts the values and takes the middle one, so a single extreme row barely moves it.

select median(duration_seconds) as median_seconds,
       quantile_cont(duration_seconds, 0.25) as p25_seconds,
       quantile_cont(duration_seconds, 0.75) as p75_seconds,
       quantile_cont(duration_seconds, 0.9) as p90_seconds
from sessions

Median, quartiles, and the 90th percentile

A percentile is a point along the sorted values. The 25th percentile has a quarter of the rows below it, the median is the 50th, and the 90th leaves only the top tenth above it. median(x) is shorthand for quantile_cont(x, 0.5). Reports on response times and session lengths often quote the 90th or 95th percentile, because it describes the slow end without being decided by the single slowest row.

Continuous or discrete

When the point you ask for falls between two rows, quantile_cont interpolates between them, so it can return a value no row has. quantile_disc returns an actual row's value instead:

select quantile_cont(duration_seconds, 0.9) as p90_cont,
       quantile_disc(duration_seconds, 0.9) as p90_disc
from sessions
where channel = 'paid'

The six paid sessions run from 81 to 600 seconds. The continuous 90th percentile is 544, part of the way from 488 to 600; the discrete one is 600, a session that happened. Use the discrete version when the answer has to be a real value, such as a price someone paid.

In other databases

median and quantile_cont are DuckDB names. PostgreSQL spells a percentile as an ordered-set aggregate, and DuckDB accepts that form too:

select percentile_cont(0.5) within group (order by duration_seconds) as median_seconds
from sessions

percentile_disc is the discrete twin. MySQL has neither, and SQL Server's PERCENTILE_CONT is a window function that needs an OVER clause.

Look at paid and social in your result. Their averages are 50 seconds above their medians, which is the shape of data with a long tail of long sessions. When the average and the median disagree that much, report the median.