How to calculate IQR in SQL

What IQR is and why analysts reach for it

You open Monday morning and the head of payments at a fintech like Stripe pings you on Slack. A finance partner just flagged that average transaction amount jumped 18% last week, but the median barely moved. They want to know whether the business actually shifted or one whale warped the mean. This is exactly where the interquartile range earns its keep — it tells you how wide the middle of the distribution is without letting a handful of extreme values rewrite the story.

IQR is the difference between the 75th and 25th percentiles, so it covers the middle 50% of your data. Because it ignores the tails by construction, IQR is robust to outliers in a way that mean and standard deviation are not. For revenue, basket size, session duration, latency, salaries — the right-skewed distributions you see at Airbnb, DoorDash, Uber, or Snowflake — IQR is usually the honest answer when someone asks "how spread out is this?"

The same number does double duty for anomaly detection. The Tukey 1.5x IQR rule gives you a defensible, distribution-free way to flag suspicious values without assuming normality. That makes it the first tool a senior analyst pulls out when reviewing data quality, sizing a fraud investigation, or sanity-checking an experiment readout. If you have ever been asked "are these numbers real?" in an interview, the right opening move is almost always quartiles, not mean and SD.

The SQL formula

The formal definition is short. Q1 is the 25th percentile of the column, Q3 is the 75th percentile, and IQR is Q3 minus Q1. In Postgres, Snowflake, BigQuery, Databricks SQL, and most modern engines you compute the percentiles with PERCENTILE_CONT inside a WITHIN GROUP clause, then subtract. The example below pulls the last 30 days of paid transactions and returns the full set of summary stats side by side so you can sanity-check the distribution before quoting it to a stakeholder.

WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY amount) AS median,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3,
        AVG(amount)    AS mean,
        STDDEV(amount) AS sd,
        COUNT(*)       AS n
    FROM transactions
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    n,
    q1,
    median,
    q3,
    q3 - q1 AS iqr,
    mean,
    sd
FROM stats;

Read the result top to bottom. If median is far below mean you are looking at a right-skewed distribution — common for revenue per user, order value, and time-to-event metrics. If iqr is small but sd is large, the bulk of the data is tight and a few extreme tails are inflating the standard deviation. That gap is the single most useful signal IQR gives you. On BigQuery use APPROX_QUANTILES(amount, 100)[OFFSET(25)] and [OFFSET(75)] for speed on billions of rows; the answer is within a fraction of a percent for any business decision.

Outlier detection with Tukey fences

Once you have Q1 and Q3, the Tukey rule is mechanical: anything below Q1 - 1.5 * IQR is a low outlier, anything above Q3 + 1.5 * IQR is a high outlier, everything in between is normal. The factor 1.5 is John Tukey's convention from exploratory data analysis. It catches roughly the most extreme 0.7% of a normal distribution, which matches what humans intuitively call "weird" for most business metrics.

WITH bounds AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
    FROM transactions
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '90 days'
),
limits AS (
    SELECT
        q1,
        q3,
        q3 - q1                       AS iqr,
        q1 - 1.5 * (q3 - q1)          AS lower_fence,
        q3 + 1.5 * (q3 - q1)          AS upper_fence,
        q1 - 3.0 * (q3 - q1)          AS extreme_lower,
        q3 + 3.0 * (q3 - q1)          AS extreme_upper
    FROM bounds
)
SELECT
    t.transaction_id,
    t.user_id,
    t.amount,
    l.lower_fence,
    l.upper_fence,
    CASE
        WHEN t.amount > l.extreme_upper THEN 'extreme high'
        WHEN t.amount > l.upper_fence   THEN 'high outlier'
        WHEN t.amount < l.extreme_lower THEN 'extreme low'
        WHEN t.amount < l.lower_fence   THEN 'low outlier'
        ELSE 'normal'
    END AS outlier_status
FROM transactions t
CROSS JOIN limits l
WHERE t.status = 'paid'
  AND t.created_at >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY t.amount DESC
LIMIT 200;

Two practical notes. First, the 3x IQR tier matters when you triage alerts — extreme outliers deserve a human eyeball, while 1.5x candidates often resolve themselves as legitimate power users or whales. Second, CROSS JOIN against a one-row CTE is cleaner than the comma-join syntax and reads better in pull request review. Use it.

IQR by group

Single-population IQR answers "how spread is my data?" Group-level IQR answers the more useful question: "where is the spread concentrated?" If a marketplace like Airbnb sees high overall IQR on nightly rate, splitting by market type or listing tier usually reveals that one segment is doing all the work. The query below ranks countries by IQR of paid transaction amount — read it as a leaderboard of "where is variance hiding?"

SELECT
    u.country,
    COUNT(*) AS n_paid,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY t.amount) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY t.amount) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY t.amount) AS q3,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY t.amount)
      - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY t.amount) AS iqr
FROM transactions t
JOIN users u USING (user_id)
WHERE t.status = 'paid'
  AND t.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.country
HAVING COUNT(*) >= 100
ORDER BY iqr DESC;

The HAVING COUNT(*) >= 100 filter is not cosmetic — percentile estimates are noisy on tiny samples, and an IQR built from 12 observations will mislead you. Set the floor high enough to defend the number in a readout. A hundred is a reasonable start for transactional data; for clickstream you probably want ten thousand.

IQR vs standard deviation

The choice between IQR and SD is the choice between robustness and assumption. SD is efficient when the data is genuinely normal, but it inherits every quirk of the tails. A single power user with a million-dollar deal can move SD by a factor of two without changing the median by a cent. That is fine if you actually want to measure the tail. It is misleading if you are describing what a typical user looks like.

SELECT
    COUNT(*) AS n,
    AVG(amount)    AS mean,
    STDDEV(amount) AS sd,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY amount) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount)
      - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS iqr,
    AVG(amount)
      - PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY amount) AS mean_median_gap
FROM transactions
WHERE status = 'paid';

For a perfect normal distribution SD ≈ IQR / 1.35, which is a useful sanity check: if your STDDEV is more than two or three times IQR / 1.35, the data is heavy-tailed and SD is no longer summarizing what a stakeholder thinks it is. Quote IQR and median in the readout, put mean and SD in a footnote.

Common pitfalls

The most common failure mode is treating the Tukey 1.5x fence as a hard rule rather than a starting heuristic. The 1.5 factor was chosen as a default in the era of paper boxplots and works well for roughly symmetric data. For latency, revenue, or anything strongly right-skewed, you will routinely see legitimate values past the upper fence; calibrate the multiplier to the metric instead of applying it blindly. A practical fix is to tune the multiplier so the daily alert queue stays at a volume the on-call team can actually triage.

Another trap is computing IQR on a binary or near-binary column. If 90% of your users have placed exactly one order, both Q1 and Q3 land on 1, IQR is zero, and the resulting rule flags everyone with two or more orders as anomalous. Discrete or low-cardinality columns need a different tool — log transformations help, but more often the right move is to bucket the metric into a count and use a Poisson-style threshold rather than IQR fences.

Small sample size is a silent killer. Percentile estimates are noisy when N is below a few hundred, and IQR inherits that noise twice over. If you run weekly anomaly detection on a freshly launched segment and the alert volume spikes the first month, the segment did not get weirder — your fences did. Stabilize the fences on a long lookback window and only refresh them on a slow cadence.

Treating any flagged value as an error is the fastest way to lose stakeholder trust. A whale spending a hundred times the median is not a data quality bug, it is a customer worth a sales call. Build the pipeline to label outliers, route them by severity, and let humans decide whether to suppress, investigate, or escalate. The IQR rule should produce a question, not a delete statement.

Finally, watch out for cumulative metrics. Lifetime value, total sessions, total spend, and similar quantities grow with tenure, so older cohorts will always look like outliers on a flat IQR rule. Compute IQR per tenure cohort or acquisition month and the fences track the natural growth curve.

Optimization tips

On large fact tables, PERCENTILE_CONT is exact and unavoidably slow because the engine has to sort the column. For interactive dashboards or anomaly jobs that run every five minutes, switch to approximate quantile primitives — APPROX_QUANTILES in BigQuery, APPROX_PERCENTILE in Snowflake and Databricks, percentile_approx in Spark SQL — and latency drops by an order of magnitude with error well under one percent. The exact version is still right for monthly board metrics, just not for production alerting.

Partitioning and clustering help more than indexes on warehouse engines. If your transactions table is partitioned by DATE(created_at) and clustered by status, the percentile scan reads only the relevant days. Pair that with a materialized view that pre-aggregates daily Q1, Q3, IQR per segment, and interactive IQR queries become single-digit-millisecond lookups instead of full table scans.

For repeated anomaly detection, cache the fences. Compute lower_fence and upper_fence once per day in a small reference table, then JOIN raw events against it. You replace a sort-based percentile per query with a hash lookup — a pattern that scales from a side project to the streaming pipeline a team at Snowflake or Databricks would ship.

Related reading

• How to detect anomalies in SQL
• How to monitor data volume anomaly in SQL
• How to calculate confidence interval in SQL
• How to calculate distribution drift in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

When should I use IQR instead of standard deviation?

Reach for IQR whenever the distribution is skewed, heavy-tailed, or contaminated with extreme values you do not want dominating the summary. That covers most business metrics — revenue, basket size, session length, latency, deal size, time-to-convert. SD is still the right answer for genuinely normal-ish data and for inputs to parametric tests, but it should not be your default for describing real-world distributions.

Where does the 1.5x IQR rule come from?

It is John Tukey's convention from the original boxplot. For a normal distribution it flags roughly the most extreme 0.7% of points, which matched the printed-page rule of thumb that an outlier should be "obvious by eye". There is nothing magical about 1.5 — many teams use 2.0 for noisier metrics or 3.0 to reserve the alert for genuine extremes. Calibrate the factor to the volume of alerts your on-call rotation can act on.

Can I use IQR on data that is not normal?

Yes, and that is precisely the appeal. IQR makes no assumption about the underlying distribution, so it gives you a robust spread estimate on bimodal, skewed, or heavy-tailed data where SD would be misleading. The only requirement is that ordering the values makes sense — which rules out categorical columns and limits how much information you can extract from low-cardinality discrete columns.

How do I compute IQR in Python alongside the SQL version?

NumPy and pandas both expose it directly. The one-liner you will write hundreds of times is below; for grouped IQR use df.groupby('country')['amount'].agg(lambda s: s.quantile(0.75) - s.quantile(0.25)).

import numpy as np
iqr = np.percentile(data, 75) - np.percentile(data, 25)

How does IQR relate to the boxplot?

A boxplot is a visual IQR readout. The box spans Q1 to Q3, the line inside is the median, the whiskers extend out to the 1.5x IQR fences, and dots beyond the whiskers are the outliers Tukey wants you to notice. If you have ever sketched a quick boxplot in matplotlib or Tableau, you have already used IQR — the SQL version just turns the same picture into a number a pipeline can act on.

What about IQR for time series?

Plain IQR ignores ordering, so it will not tell you that a metric is trending. The standard fix is to compute a rolling IQR on a trailing window — for example, last 28 days — and use those fences to flag anomalies on the latest point. Pair it with a slow-moving median and you have a defensible, robust, distribution-free anomaly detector that holds up in a production review.