Skip to content
Tutorials Center
My learning

Topics

Lesson 54 of 125 ยท Window Functions

Percentiles, Medians and Statistics

Compute medians and percentiles in SQL, use PERCENT_RANK and CUME_DIST, calculate standard deviation and z-scores, and solve gaps-and-islands problems with windows.

Averages hide a lot. A department where everyone earns 80,000 and one where half earn 40,000 and half earn 120,000 have the same average, but they are very different places to work. To describe data honestly you need distribution statistics: medians, percentiles, spread, and the position of each value within the whole.

This lesson covers the two distribution window functions, PERCENT_RANK and CUME_DIST, then shows how to compute medians and arbitrary percentiles in SQLite (which has no standard PERCENTILE_CONT), standard deviation and z-scores, and finally the classic gaps and islands technique for finding streaks and missing ranges. It's the last lesson of the window functions chapter, and it uses everything from the previous ones.

PERCENT_RANK and CUME_DIST

Both functions tell you where a row stands in the ordered partition, as a number between 0 and 1:

  • PERCENT_RANK() = (rank โˆ’ 1) / (rows โˆ’ 1). The first row is 0, the last is 1. It answers "what fraction of the other rows rank below me?"
  • CUME_DIST() = (rows with a value โ‰ค mine) / rows. It's never 0, and the last row is 1. It answers "what fraction of all rows are at or below me?"
SQL PERCENT_RANK and CUME_DIST ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

There are 36 employees. Amara, the lowest earner, has pct_rank 0 and cume_dist 0.028 (1/36). Look at the two people earning 62,000: they share rank 5, so both get a pct_rank of 4/35 = 0.114, and both get a cume_dist of 6/36 = 0.167, because six people earn 62,000 or less. Ties always get equal values in both functions.

A common use is labeling rows by percentile: "this student is in the top 10% of the class" means CUME_DIST() OVER (ORDER BY grade DESC) <= 0.10. Here are the school's best results in Calculus I, with each grade's percentile:

SQL Top grades by percentile ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The WHERE keeps students in the top quarter of the grade distribution. Note the grade IS NOT NULL filter: ungraded enrollments would otherwise be sorted first and distort every percentile.

The median

The median is the middle value: half the values are below it, half above. With an odd count it's the middle row; with an even count it's the average of the two middle rows. It resists outliers: the CEO's 210,000 salary pulls the average up but barely moves the median.

Standard SQL computes it with PERCENTILE_CONT(0.5), which SQLite doesn't have. The portable recipe uses two windows, a row number and a count, then averages the middle row(s):

SQL Median salary, portable version ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The trick is in the WHERE. SQLite's integer division makes (n + 1) / 2 and (n + 2) / 2 the same row when n is odd, and the two middle rows when n is even. With 36 employees, rows 18 and 19 are averaged: 78,000 and 80,000 give a median of 79,000, while the mean is 90,361.

Add PARTITION BY to both windows and GROUP BY to the outer query to get a median per group:

SQL Median salary per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In every department the mean is well above the median, because each head earns far more than their team. Design is the extreme case: a median of 69,000 against a mean of 83,000.

This site's SQLite has a shortcut. The SQLite build used in these examples includes an extension with aggregate functions median(), lower_quartile(), upper_quartile(), stdev() and variance(), so SELECT median(salary) FROM employees; also returns 79,000 here. A standard SQLite installation doesn't have them (recent versions offer an optional "percentile" extension instead), so prefer the portable recipe in code that must run anywhere.

Any percentile, interpolated

The median is the 50th percentile. For others, such as the 90th percentile of salaries, you need a rule for values that fall between two rows. PERCENTILE_CONT(p) uses linear interpolation: it computes the position 1 + p ร— (n โˆ’ 1) and, if that falls between rows, blends their values. You can do the same in SQLite:

SQL PERCENTILE_CONT by hand ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

For 36 rows, position 1 + 0.9 ร— 35 = 32.5, halfway between the 32nd salary (138,000) and the 33rd (146,000), so the 90th percentile is 142,000. Change 0.9 to 0.5 and you get the median again, 79,000.

There's also a discrete variant, PERCENTILE_DISC(p), which never interpolates: it returns the first actual value whose CUME_DIST is at least p. In SQLite that's SELECT MIN(salary) FROM (... CUME_DIST() ...) WHERE cume_dist >= 0.9.

In other databases, percentiles are built in as ordered-set aggregates:

SQL
-- PostgreSQL and Oracle: aggregate form
SELECT
  department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY department_id;

-- SQL Server: only as a window function, so add DISTINCT to get one row per group
SELECT DISTINCT
  department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
    OVER (PARTITION BY department_id) AS median_salary
FROM employees;
Database Median / percentiles
PostgreSQL PERCENTILE_CONT, PERCENTILE_DISC (aggregate), also mode() WITHIN GROUP
Oracle Same as PostgreSQL, plus MEDIAN()
SQL Server PERCENTILE_CONT, PERCENTILE_DISC as window functions only
MySQL / MariaDB MySQL: none, use the ROW_NUMBER recipe; MariaDB 10.3+: window forms like SQL Server
SQLite None by default; use the recipes above

Spread: standard deviation and z-scores

The standard deviation measures how far values typically are from the mean. Standard SQL spells it STDDEV_POP and STDDEV_SAMP (PostgreSQL, MySQL, Oracle; SQL Server says STDEVP/STDEV). Portable SQLite computes it from its definition with the math functions:

  • population variance = average of xยฒ โˆ’ (average of x)ยฒ
  • sample variance = population variance ร— n / (n โˆ’ 1)

A z-score expresses each value as "how many standard deviations above or below the mean". Combining the aggregates with windows gives every employee a z-score in one query:

SQL Standard deviation and z-scores ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The population standard deviation is about 34,921. The CEO's z-score is 3.43, far out on the tail; values beyond ยฑ3 are commonly flagged as outliers. (With the sample formula the deviation is 35,417, matching what stdev() returns in this site's SQLite.)

Precision: the "average of squares minus square of average" formula can lose precision on huge values or values with tiny spread, because it subtracts two large, nearly equal numbers. For salaries it's fine; for scientific data, prefer a database with a built-in STDDEV_SAMP, or center the values (subtract the mean) first.

Correlation, covariance and regression

Standard deviation describes one column. Often you want to know how two columns move together: do employees who have been around longer earn more? Three related numbers answer that:

  • Covariance says whether two columns rise together (positive), move in opposite directions (negative) or ignore each other (near zero). Its size depends on the units, so it is hard to compare.
  • Correlation (Pearson's r) is the covariance divided by both standard deviations. It always lies between โˆ’1 and 1, which makes it comparable: 0.9 is a strong positive link, 0 is none, โˆ’0.9 is a strong negative link.
  • Linear regression fits the straight line y = intercept + slope ร— x that best describes the cloud of points. The slope tells you how much y changes per unit of x, and Rยฒ (the square of the correlation) is the share of the variation in y that the line explains.

PostgreSQL, Oracle and a few others ship these as aggregate functions; MySQL and SQL Server don't (MySQL has only VAR_POP/VAR_SAMP, SQL Server has VAR/VARP):

Quantity PostgreSQL / Oracle Hand-built (works everywhere)
Population covariance COVAR_POP(y, x) AVG(x*y) - AVG(x)*AVG(y)
Sample covariance COVAR_SAMP(y, x) population covariance ร— n / (n โˆ’ 1)
Population / sample variance VAR_POP(x) / VAR_SAMP(x) AVG(x*x) - AVG(x)*AVG(x), then ร— n / (n โˆ’ 1)
Correlation CORR(y, x) covariance / sqrt(var_x * var_y)
Slope REGR_SLOPE(y, x) covariance / var_x
Intercept REGR_INTERCEPT(y, x) AVG(y) - slope * AVG(x)
Rยฒ REGR_R2(y, x) corr * corr
Number of pairs REGR_COUNT(y, x) COUNT(*) where both are not NULL

Mind the argument order: the standard functions take the dependent value y first and the independent value x second. Every one of them silently skips rows where either value is NULL, so filter explicitly if you build the numbers by hand.

SQLite has none of the functions, but the right-hand column only needs AVG, so you can compute everything in one pass. Here x is years of service on 1 January 2026 and y is salary:

SQL Does tenure explain salary? ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The correlation across the 36 employees is 0.6406, so tenure and pay clearly move together; Rยฒ is about 0.41, meaning the line explains roughly 41% of the variation in salary. The slope of about 8,385 reads as "each extra year of service goes with roughly 8,400 more salary", starting from an intercept near 47,300 for a brand-new hire. The same query in PostgreSQL is just SELECT CORR(salary, years), REGR_SLOPE(salary, years), REGR_INTERCEPT(salary, years) FROM ....

Because the formulas only use AVG, adding GROUP BY gives one regression per group. This version feeds a grouping column through the CTEs and compares departments that have at least four people:

SQL Correlation per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Design is the steepest (slope 12,754 per year, r = 0.886) and Support the flattest (slope 3,038, r = 0.347). Treat groups of four or five people with care: a correlation computed from so few points swings wildly with a single new hire.

Correlation is not causation. A strong r tells you two columns move together, not why. Here seniority and pay are tangled up with job title and department. Also remember that r only measures straight-line relationships (a perfect U-shaped pattern can give r = 0), that one outlier can drag it a long way, and that a constant column has zero variance, so the division fails: guard with NULLIF(var_x, 0) when groups can be uniform. The "average of products minus product of averages" shortcut also shares the precision caveat from the standard deviation section above.

Gaps and islands

"Gaps and islands" is the name for problems about consecutive runs: login streaks, uninterrupted machine uptime, seats in a row, ranges of missing invoice numbers. The islands are the runs; the gaps are the holes between them.

Finding islands (streaks)

The famous trick: within an ordered run of consecutive values, the value minus its row number is constant. Day 5 is row 1, day 6 is row 2, day 7 is row 3: 5 โˆ’ 1 = 6 โˆ’ 2 = 7 โˆ’ 3 = 4. When a day is skipped, the difference jumps. So that difference identifies each island, and you can GROUP BY it:

SQL Login streaks

Press Run (or Ctrl + Enter) to see the result here.

Ana has three streaks (3, 2 and 1 days) and Ben has two (4 days and 1 day). Wrap it in another CTE and take MAX(days_in_a_row) per user to get each user's longest streak. If a user can log in several times a day, deduplicate first with SELECT DISTINCT user_name, day, or use DENSE_RANK instead of ROW_NUMBER.

Finding gaps

Gaps are easier: compare each value with the next one using LEAD, and report where the jump is bigger than 1. This finds missing invoice numbers:

SQL Missing invoice numbers

Press Run (or Ctrl + Enter) to see the result here.

Two gaps appear: 1004โ€“1005 and 1008โ€“1009, two missing numbers each. Auditors ask exactly this question, and the same query works on dates with julianday().

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a partition of 5 rows with no ties, what is PERCENT_RANK of the last row and CUME_DIST of the first row?

    PERCENT_RANK = (5 - 1) / (5 - 1) = 1 for the last row. CUME_DIST = 1 / 5 = 0.2 for the first row; it's never 0.
  2. 2. Values are 10, 20, 30, 100. What is the median?

    With an even count, the median is the average of the two middle values, (20 + 30) / 2 = 25. The mean would be 40, pulled up by the outlier.
  3. 3. Why does the median recipe use rn IN ((n + 1) / 2, (n + 2) / 2)?

    For n = 5 both expressions give 3; for n = 6 they give 3 and 4. Averaging the selected rows gives the median in both cases.
  4. 4. In the islands technique, why does julianday(day) - ROW_NUMBER() identify a streak?

    Consecutive days increase in step with the row number. A missing day makes the date jump ahead, which changes the difference and starts a new island.
  5. 5. Which database computes a median with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) as a normal aggregate with GROUP BY?

    PostgreSQL (and Oracle) offer the aggregate form. SQL Server only has the window form, and MySQL and standard SQLite have neither.

Summary

  • PERCENT_RANK() = (rank โˆ’ 1) / (n โˆ’ 1) and CUME_DIST() = rows at or below / n; both place a row within its distribution.
  • The median is the middle value; compute it portably with ROW_NUMBER() and COUNT(*) OVER () and average the middle row(s).
  • PERCENTILE_CONT interpolates between rows and PERCENTILE_DISC returns an actual value; SQLite needs the manual recipes, other databases have them built in.
  • Standard deviation comes from sqrt(avg(xยฒ) โˆ’ avg(x)ยฒ) in SQLite; z-scores show how unusual each value is.
  • Correlation, covariance and regression come from CORR/COVAR_POP/REGR_SLOPE where they exist, or from AVG(x*y) - AVG(x)*AVG(y) and friends anywhere; correlation shows co-movement, never cause.
  • Islands: group by value โˆ’ ROW_NUMBER(); gaps: compare each value with LEAD().

Next: INSERT, the start of the Changing Data chapter, where you'll add rows to tables.