How to calculate z-score in SQL

Step by step

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

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

The query

SELECT ROUND((d.website_orders - s.m) / s.sd, 2) AS z_day_17
FROM daily d,
     (SELECT AVG(website_orders) AS m, STDDEV_SAMP(website_orders) AS sd FROM daily) s
WHERE d.day = 17;

Expected result on the sample data: 4.04.

What the result means

Day 17 had 61 website orders. Its z-score is computed with a mean and standard deviation that include day 17 itself, which shrinks the score; leaving the day out of the baseline gives a much larger one.

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 z-score misleads

• Outliers inflate the standard deviation and so hide themselves. For outlier hunting, a robust rule (IQR, or median absolute deviation) is safer.
• The "beyond 3 is rare" rule assumes roughly normal data. Skewed data produces large z-scores routinely.
• Use the same standard deviation version (sample or population) consistently; STANDARDIZE takes whichever you give it.

The plain-language explanation is on the Z-score page.

The data

Same method, other tools

• Z-score in Excel and Google Sheets
• Z-score in Python (pandas)

Related methods

• How to calculate standard deviation in SQL
• How to calculate finding outliers with the IQR rule in SQL
• How to calculate percentile in SQL

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.