{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"percentiles-and-distributions","title":"Percentiles: describing a distribution instead of its mean","url":"https://addaly.com/learn/data-and-sql/percentiles-and-distributions","position":26,"of":66,"minutes":9,"answer":"A percentile answers \"what value does a given share of rows fall below?\", and unlike sums, percentiles from two groups cannot be combined, so the raw rows or a sketch must be kept.","keyIdea":"A percentile answers \"what value does a given share of rows fall below?\", and unlike sums, percentiles from two groups cannot be combined, so the raw rows or a sketch must be kept."},"body":{"format":"markdown","text":"## One number cannot describe a shape\n\nA pile of 100,000 page-load times has a mean of 410 ms. That is true and nearly useless. It could mean every page loads in about 400 ms, or that 95% load in 200 ms and 5% take eight seconds. Those are different products, and the people who hit the eight seconds are the ones who leave.\n\nA percentile answers a question the mean cannot: \"what is the value that a given share of rows fall below?\" The median is the 50th percentile, the point half the rows sit under. The 95th percentile, written p95, is the value that 95% of rows are under and 5% are over.\n\n## Getting one out of SQL\n\nPostgres uses a syntax that reads oddly the first time:\n\n```sql\nSELECT\n  percentile_cont(0.5)  WITHIN GROUP (ORDER BY load_ms) AS p50,\n  percentile_cont(0.95) WITHIN GROUP (ORDER BY load_ms) AS p95,\n  percentile_cont(0.99) WITHIN GROUP (ORDER BY load_ms) AS p99,\n  avg(load_ms)                                          AS mean\nFROM page_loads\nWHERE loaded_at >= '2026-03-01' AND loaded_at < '2026-04-01';\n```\n\n`percentile_cont` interpolates between the two nearest rows, so it can return a value no row actually has. `percentile_disc` returns a real row's value. For latency and money the difference is cosmetic; for something like \"the median number of children per household\" `percentile_disc` avoids reporting 1.5 children.\n\nDuckDB has `quantile_cont(load_ms, 0.95)` and a plain `median(load_ms)`. MySQL and SQLite have no percentile function; you sort, number the rows with a window function, and pick the row at position `ceil(0.95 * n)`, which the next module covers. Spreadsheets have `PERCENTILE` and `MEDIAN`, and LibreOffice Calc's are the same as Excel's.\n\n## Why p99 is the number that matters most\n\nIf 1% of requests are slow, that sounds negligible. At a million requests a day it is ten thousand slow experiences, and because a page makes many requests, a user's chance of hitting at least one p99 event is far higher than 1%. Twenty requests per page at a 1% tail gives roughly an 18% chance the page contains a slow one. This is why engineering teams track p99 rather than the mean: the mean is dominated by the fast majority and hides the tail entirely.\n\n## Seeing the whole shape\n\nPercentiles are samples of the distribution. A histogram is the distribution. `width_bucket` sorts values into equal-width bins:\n\n> Figure: One month of page loads: the mean, and the shape it hides. Bars (ms): Median (p50) 180; Mean 410; p90 640; p95 1250; p99 4900. The mean sits between the median and the 90th percentile and describes neither. The p99 is twenty-seven times the median, and at a million requests a day that tail is ten thousand slow experiences. Report p50, p95 and p99 beside the mean and the count.\n\n```sql\nSELECT width_bucket(amount, 0, 5000, 10) AS bucket,\n       min(amount), max(amount), count(*)\nFROM orders\nGROUP BY bucket\nORDER BY bucket;\n```\n\nTen bins of ₹500 between 0 and 5,000, with bucket 11 catching everything above. Read the counts down the column and you can see whether there is one hump, two humps, or a wall at a price point. Two humps usually means two populations in one table, such as retail and wholesale customers, and that is a finding on its own.\n\n## The limitation that is a mechanism, not a policy\n\nYou can add two sums. You cannot combine two medians. If server A has a p95 of 300 ms and server B has a p95 of 900 ms, the p95 across both is not 600 ms and cannot be computed from the two figures at all. It depends on how many requests each server handled and where every request fell. An exact percentile needs the raw values, sorted, which is why the query above has to read and sort all 100,000 rows, and why a summary table holding `p95` per hour cannot give you p95 per day.\n\nLarge engines make a trade here. BigQuery's `APPROX_QUANTILES`, Spark's `approx_percentile` and DuckDB's `approx_quantile` use sketches that hold a compressed picture of the distribution in a few kilobytes, can be merged across servers and hours, and are wrong by a small stated amount, typically under 1% of the rank. For a dashboard that is the right trade. For a service-level agreement with money attached, run the exact version and accept the sort.\n\n## Reporting them\n\nPut p50, p95 and p99 in the same row as the mean and the count. A mean far above the median says \"long tail\". A p99 ten times p50 says \"a small group is having a very different experience\". The count says how many rows the tail is, which is the number somebody will ask for next."},"check":{"question":"Two web servers each report their own p95 response time for the day: 300 ms and 900 ms. A manager asks for the p95 across both servers combined. What can you say from these two figures alone?","options":[{"text":"It is 600 ms, the mean of the two, since each server contributed one day's worth of requests.","correct":false,"tempting":"Averages percentiles as if they were sums; the combined p95 depends on how many requests each server handled and where every request fell."},{"text":"It is 900 ms, because the combined 95th percentile must equal the worse of the two.","correct":false,"tempting":"Would be true only if server B handled at least 5% of all traffic and every one of its slow requests outranked A's; nothing here says so."},{"text":"Nothing exact; it lies somewhere between 300 and 900 ms and needs the raw values or a mergeable sketch to compute.","correct":true},{"text":"It is 300 ms weighted by request counts, because p95 is the value 95% of requests fall under and most requests are fast.","correct":false,"tempting":"Uses the request-count weighting that works for a mean, but a percentile is a position in a sorted list, not a weighted sum."}]},"prev":{"slug":"averages-that-lie","title":"Averages: the four ways AVG misleads","url":"https://addaly.com/learn/data-and-sql/averages-that-lie"},"next":{"slug":"grouping-by-time","title":"Grouping by day, week and month without lying about the boundaries","url":"https://addaly.com/learn/data-and-sql/grouping-by-time"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Percentiles: describing a distribution instead of its mean', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/percentiles-and-distributions (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Percentiles: describing a distribution instead of its mean","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"A percentile answers “what value does a given share of rows fall below?”, and unlike sums, percentiles from two groups cannot be combined, so the raw rows or a sketch must be kept.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"ms","title":"One month of page loads: the mean, and the shape it hides","series":[{"label":"Median (p50)","value":180},{"label":"Mean","value":410},{"label":"p90","value":640},{"label":"p95","value":1250},{"label":"p99","value":4900,"highlight":true}],"caption":"The mean sits between the median and the 90th percentile and describes neither. The p99 is twenty-seven times the median, and at a million requests a day that tail is ten thousand slow experiences. Report p50, p95 and p99 beside the mean and the count."},"alt":"Figure: One month of page loads: the mean, and the shape it hides. Bars (ms): Median (p50) 180; Mean 410; p90 640; p95 1250; p99 4900. The mean sits between the median and the 90th percentile and describes neither. The p99 is twenty-seven times the median, and at a million requests a day that tail is ten thousand slow experiences. Report p50, p95 and p99 beside the mean and the count."}},"blocks":[{"t":"h2","id":"one-number-cannot-describe-a-shape","runs":[{"text":"One number cannot describe a shape"}]},{"t":"p","runs":[{"text":"A pile of 100,000 page-load times has a mean of 410 ms. That is true and nearly useless. It could mean every page loads in about 400 ms, or that 95% load in 200 ms and 5% take eight seconds. Those are different products, and the people who hit the eight seconds are the ones who leave."}]},{"t":"p","runs":[{"text":"A percentile answers a question the mean cannot: “what is the value that a given share of rows fall below?” The median is the 50th percentile, the point half the rows sit under. The 95th percentile, written p95, is the value that 95% of rows are under and 5% are over."}]},{"t":"h2","id":"getting-one-out-of-sql","runs":[{"text":"Getting one out of SQL"}]},{"t":"p","runs":[{"text":"Postgres uses a syntax that reads oddly the first time:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  percentile_cont(0.5)  WITHIN GROUP (ORDER BY load_ms) AS p50,\n  percentile_cont(0.95) WITHIN GROUP (ORDER BY load_ms) AS p95,\n  percentile_cont(0.99) WITHIN GROUP (ORDER BY load_ms) AS p99,\n  avg(load_ms)                                          AS mean\nFROM page_loads\nWHERE loaded_at >= '2026-03-01' AND loaded_at < '2026-04-01';"},{"t":"p","runs":[{"text":"percentile_cont","code":true},{"text":" interpolates between the two nearest rows, so it can return a value no row actually has. "},{"text":"percentile_disc","code":true},{"text":" returns a real row’s value. For latency and money the difference is cosmetic; for something like “the median number of children per household” "},{"text":"percentile_disc","code":true},{"text":" avoids reporting 1.5 children."}]},{"t":"p","runs":[{"text":"DuckDB has "},{"text":"quantile_cont(load_ms, 0.95)","code":true},{"text":" and a plain "},{"text":"median(load_ms)","code":true},{"text":". MySQL and SQLite have no percentile function; you sort, number the rows with a window function, and pick the row at position "},{"text":"ceil(0.95 * n)","code":true},{"text":", which the next module covers. Spreadsheets have "},{"text":"PERCENTILE","code":true},{"text":" and "},{"text":"MEDIAN","code":true},{"text":", and LibreOffice Calc’s are the same as Excel’s."}]},{"t":"h2","id":"why-p99-is-the-number-that-matters-most","runs":[{"text":"Why p99 is the number that matters most"}]},{"t":"p","runs":[{"text":"If 1% of requests are slow, that sounds negligible. At a million requests a day it is ten thousand slow experiences, and because a page makes many requests, a user’s chance of hitting at least one p99 event is far higher than 1%. Twenty requests per page at a 1% tail gives roughly an 18% chance the page contains a slow one. This is why engineering teams track p99 rather than the mean: the mean is dominated by the fast majority and hides the tail entirely."}]},{"t":"h2","id":"seeing-the-whole-shape","runs":[{"text":"Seeing the whole shape"}]},{"t":"p","runs":[{"text":"Percentiles are samples of the distribution. A histogram is the distribution. "},{"text":"width_bucket","code":true},{"text":" sorts values into equal-width bins:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT width_bucket(amount, 0, 5000, 10) AS bucket,\n       min(amount), max(amount), count(*)\nFROM orders\nGROUP BY bucket\nORDER BY bucket;"},{"t":"p","runs":[{"text":"Ten bins of ₹500 between 0 and 5,000, with bucket 11 catching everything above. Read the counts down the column and you can see whether there is one hump, two humps, or a wall at a price point. Two humps usually means two populations in one table, such as retail and wholesale customers, and that is a finding on its own."}]},{"t":"h2","id":"the-limitation-that-is-a-mechanism-not-a-policy","runs":[{"text":"The limitation that is a mechanism, not a policy"}],"role":"limit"},{"t":"p","runs":[{"text":"You can add two sums. You cannot combine two medians. If server A has a p95 of 300 ms and server B has a p95 of 900 ms, the p95 across both is not 600 ms and cannot be computed from the two figures at all. It depends on how many requests each server handled and where every request fell. An exact percentile needs the raw values, sorted, which is why the query above has to read and sort all 100,000 rows, and why a summary table holding "},{"text":"p95","code":true},{"text":" per hour cannot give you p95 per day."}]},{"t":"p","runs":[{"text":"Large engines make a trade here. BigQuery’s "},{"text":"APPROX_QUANTILES","code":true},{"text":", Spark’s "},{"text":"approx_percentile","code":true},{"text":" and DuckDB’s "},{"text":"approx_quantile","code":true},{"text":" use sketches that hold a compressed picture of the distribution in a few kilobytes, can be merged across servers and hours, and are wrong by a small stated amount, typically under 1% of the rank. For a dashboard that is the right trade. For a service-level agreement with money attached, run the exact version and accept the sort."}]},{"t":"h2","id":"reporting-them","runs":[{"text":"Reporting them"}]},{"t":"p","runs":[{"text":"Put p50, p95 and p99 in the same row as the mean and the count. A mean far above the median says “long tail”. A p99 ten times p50 says “a small group is having a very different experience”. The count says how many rows the tail is, which is the number somebody will ask for next."}]}],"check":{"question":[{"text":"Two web servers each report their own p95 response time for the day: 300 ms and 900 ms. A manager asks for the p95 across both servers combined. What can you say from these two figures alone?"}],"options":[{"text":[{"text":"It is 600 ms, the mean of the two, since each server contributed one day’s worth of requests."}],"correct":false,"tempting":[{"text":"Averages percentiles as if they were sums; the combined p95 depends on how many requests each server handled and where every request fell."}]},{"text":[{"text":"It is 900 ms, because the combined 95th percentile must equal the worse of the two."}],"correct":false,"tempting":[{"text":"Would be true only if server B handled at least 5% of all traffic and every one of its slow requests outranked A’s; nothing here says so."}]},{"text":[{"text":"Nothing exact; it lies somewhere between 300 and 900 ms and needs the raw values or a mergeable sketch to compute."}],"correct":true,"tempting":null},{"text":[{"text":"It is 300 ms weighted by request counts, because p95 is the value 95% of requests fall under and most requests are fast."}],"correct":false,"tempting":[{"text":"Uses the request-count weighting that works for a mean, but a percentile is a position in a sorted list, not a weighted sum."}]}],"order":[1,3,2,0],"why":null},"prev":{"slug":"averages-that-lie","title":"Averages: the four ways AVG misleads","minutes":9},"next":{"slug":"grouping-by-time","title":"Grouping by day, week and month without lying about the boundaries","minutes":10}}}