How to calculate interquartile range in SQL

SQL query for interquartile range, run on a downloadable 30-day sample table, with the expected result and which databases support it.

In SQL, interquartile range over the sample table is calculated with the query below. On the sample data it returns 6.75.

IQR = Third quartile (75th percentile) − First quartile (25th percentile)

Step by step

Load daily.csv into a table called daily (day, ad_spend, website_orders, app_orders, avg_basket).
Run the query and compare with the expected result.

Table

CREATE TABLE daily (
  day INTEGER,
  ad_spend INTEGER,
  website_orders INTEGER,
  app_orders INTEGER,
  avg_basket INTEGER
);

The query

Query

SELECT
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY website_orders)
  - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY website_orders) AS iqr_orders
FROM daily;

Expected result on the sample data: 6.75.

What the result means

Quartiles can be computed in several ways. The inclusive method used here (QUARTILE.INC, pandas quantile, PERCENTILE_CONT) interpolates between values and is the most common. On this data the IQR hardly notices the sale on day 17, while the range and standard deviation both jump because of it, which is exactly why the IQR is used for outlier rules.

Notes for SQL

Tested on DuckDB. The same functions exist in PostgreSQL. SQLite and MySQL lack most statistical aggregates; compute in Python or load the data into DuckDB, which can query a CSV file directly.
Statistical aggregates ignore NULL rows. Decide whether a missing day should be NULL (left out) or 0 (counted as a zero day).

Where interquartile range misleads

QUARTILE.INC and QUARTILE.EXC give different answers on small datasets. Use the same method everywhere.
The IQR says nothing about the tails. Report it with the median, or draw a box plot.
For very small samples (under ten values) quartiles are unstable.

The plain-language explanation is on the Interquartile range (IQR) page.

The data

daily.csv (made up): 30 days from one store
day	website_orders
1	24
2	27
3	22
4	30
5	28
6	25
7	31
8	29
9	26
10	33
11	30
12	28
13	35
14	32
15	29
16	34
17	61
18	31
19	36
20	33
21	30
22	37
23	35
24	32
25	38
26	36
27	34
28	40
29	37
30	39

Same method, other tools

Related methods

Learn more about SQL

Python and SQL for data analysis, side by side — 3 min read
Data analysis methods and techniques, and when to use each — 3 min read

About the data on this page

The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as daily.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.

To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.

Legal entity

Devansh Yadav

Moradabad, Uttar Pradesh

Governing law: Courts at Moradabad, Uttar Pradesh

Phone: +91 8433475698

Legal: legal@analyzeit.in