Snowflake SQL Tutorial 21 - MEDIAN, STDDEV and Percentiles in Snowflake

Snowflake SQL · Episode 21

In this post we are going to cover the statistical aggregates in Snowflake: MEDIAN, MODE, STDDEV, VARIANCE and the two percentile functions.

These are ordinary aggregates. They take a column, read every row, and give us back one number. What changes is the question they answer. AVG tells us where the balance point of a column is. These tell us where the middle is, how far apart the values are, and what the value is at a given point in the distribution.

We will take them one at a time, on a table small enough to check by hand. Along the way we will sort out the difference between STDDEV and STDDEV_POP with two multiplications we can redo ourselves, instead of a formula we have to trust.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 21 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with MEDIAN and AVG side by side
3. Now let's ask for MODE
4. Now let's look at the two standard deviations
5. What do STDDEV and STDDEV_POP actually differ by?
6. Now let's do the same statistics per group
7. Let's compare PERCENTILE_CONT and PERCENTILE_DISC
8. Now let's run the same three on a wider column
9. One last thing: CORR and COVAR, and a warning

Let's create some sample data

Statistics on a table we cannot see are just numbers, so let's start with the table. Almost everything in this post runs against EMPLOYEES, and the column it measures is SALARY.

Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so if you have run it already you are ready to go.

Sample data

-- Sample data for this post.
-- Run this once. Every post in this series uses the same EMPLOYEES table.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Two things in that script decide most of what happens below. It loads eight employees, which is a small enough set that we can check any statistic in this post by hand. And one of those eight, Daniel, is paid 131000.00, well above everybody else.

That single salary is why the first two functions we run disagree with each other.

One other table

One query near the end measures the ORDERS table instead, which is created by the same shared sample-data script and was the subject of episode 14. Everything else here is EMPLOYEES.

Let's start with MEDIAN and AVG side by side

MEDIAN and AVG both claim to tell us where the centre of a column is. Let's ask them both at the same time.

Query

SELECT MEDIAN(SALARY) AS MEDIAN_SALARY, AVG(SALARY) AS MEAN_SALARY FROM EMPLOYEES;

Result1 row · 2 columns

MEDIAN_SALARY	MEAN_SALARY
96500.00000	99581.25000000

MEDIAN_SALARY is 96500.00000. MEAN_SALARY is 99581.25000000. Same column, same eight rows, and the mean is the higher of the two.

Why they differ

MEDIAN is the middle value. Half the employees earn more than it and half earn less. It does not care how much more. It only cares which side of the middle each person falls on.

AVG is the balance point. Every value pulls on it in proportion to its size, so one value at the end of the range can drag it a long way. Daniel's 131000.00 is exactly that value, and it pulls the mean above the middle person.

That is not AVG being broken. That is AVG doing precisely its job. It is a reason to look at both. When the two disagree, the gap is telling us something about the shape of the data rather than about its centre. A report that quotes only the mean has hidden that from whoever reads it.

Now let's ask for MODE

The third measure of centre is MODE. Not the middle and not the balance point, but whichever value turns up most often. It is the only one of the three that makes sense on a column that is not really a quantity, which is why we are running it on DEPT_ID.

Query

SELECT MODE(DEPT_ID) AS MOST_COMMON_DEPT FROM EMPLOYEES;

Result1 row · 1 column

MOST_COMMON_DEPT
10

Department 10, because department 10 has three employees in it and the others have fewer. We can confirm that against the per-department grid further down this page, which counts each department.

What this page will not tell you

There is no tie in this data, so this query says nothing whatsoever about what MODE does when two values are equally common. That has not been tested here, so this page does not tell you. Not which one it picks, and not whether the choice is stable between runs. If ties are possible in your column, check it before you rely on the answer.

Now let's look at the two standard deviations

Centre is one question. Spread is the other. Not where the middle is, but how far apart the values are. Snowflake has two functions for it, so let's run both.

Query

SELECT STDDEV(SALARY) AS STDDEV_SAMP, STDDEV_POP(SALARY) AS STDDEV_POPULATION FROM EMPLOYEES;

Result1 row · 2 columns

STDDEV_SAMP	STDDEV_POPULATION
18394.95151043972	17206.901549596314

STDDEV returns 18394.95151043972. STDDEV_POP returns 17206.901549596314. Same column, same eight rows, two different answers. And everybody's first question is which one is right.

Both. They answer slightly different questions. The next section shows exactly what the difference is, instead of asking us to believe a formula.

What do STDDEV and STDDEV_POP actually differ by?

Standard deviation is the square root of variance, and variance has the same two versions. Let's ask for both of those.

Query

SELECT VARIANCE(SALARY) AS VAR_SAMP, VAR_POP(SALARY) AS VAR_POPULATION FROM EMPLOYEES;

Result1 row · 2 columns

VAR_SAMP	VAR_POPULATION
338374241.0714285714	296077460.9375000000

Before anything else, that grid confirms the sentence above it. Take the square root of each of those two numbers and you land back on the two values from the previous section, 18394.95151043972 and 17206.901549596314. Nothing in this post needs you to take “STDDEV is the square root of VARIANCE” on faith. It is a calculator away.

Now the part worth doing yourself

Take the sample variance and multiply it by seven. Then take the population variance and multiply it by eight.

Captured value	Multiplied by	Product
VAR_SAMP  338374241.0714285714	7  (n − 1)	2,368,619,687.5
VAR_POPULATION  296077460.9375000000	8  (n)	2,368,619,687.5

The same number, both times. That is not a coincidence, and it is not a captured result. It is arithmetic on the two values in the grid above, and the whole point is that you can redo it yourself in a few seconds.

That number is the sum of the squared differences from the mean. Both functions compute it. The only difference between them is what they then divide it by.

Function pair	Divides by	Use it when
VARIANCE / STDDEV	n − 1, which is 7 here	Your rows are a sample you are using to estimate something bigger
VAR_POP / STDDEV_POP	n, which is 8 here	Your rows are the whole population you care about

That is the entire difference, and it is also the whole of the advice. Which one you want is a statistics question rather than a SQL one, and this page is not going to pretend otherwise. But you now know precisely what you are choosing between.

Which one is the default

The plain names are the sample versions. STDDEV and VARIANCE divide by n − 1, and you have to ask for _POP to get the other one. If you have never thought about which you wanted, you have been using the sample version.

Now let's do the same statistics per group

Everything so far has described the whole table in one row. These functions are ordinary aggregates, so GROUP BY works on them exactly as it does on COUNT and AVG earlier in this level.

Query

SELECT DEPT_ID, COUNT(*) AS N, AVG(SALARY) AS MEAN, MEDIAN(SALARY) AS MED, STDDEV(SALARY) AS SD
FROM EMPLOYEES GROUP BY DEPT_ID ORDER BY DEPT_ID;

Result4 rows · 5 columns

DEPT_ID	N	MEAN	MED	SD
10	3	115166.66666667	118000.00000	17423.642940938997
20	2	95625.00000000	95625.00000	11844.038584874672
30	2	83950.00000000	83950.00000	17748.380207782342
NULL	1	92000.00000000	92000.00000	NULL

Four departments, each with its own count, mean, median and standard deviation. The N column is also where the MODE result earlier gets confirmed. Department 10 has the largest count.

The last row

The NULL department has N of 1, and its SD comes back NULL.

That is not a bug, and it has nothing to do with NULLs in the data. Read it off the table in the previous section instead. The sample standard deviation divides by n − 1. There is one row in that group, so n − 1 is zero, and we cannot divide by zero. There is no answer, so the answer is NULL.

Before this page you would have seen that cell and wondered. Having done the multiplication above, you can simply read it.

Worth knowing

A one-row group is not a strange edge case. It turns up the moment you group real data by anything with a long tail. If a NULL standard deviation would be awkward downstream, the fix is to decide what a group of one means for your report, not to go looking for bad data.
What STDDEV_POP returns for that same group was not captured here, so this page does not tell you. Run it.

Let's compare PERCENTILE_CONT and PERCENTILE_DISC

The last pair is the one with an actual choice in it. Both functions answer “what is the value at this point in the distribution”, and both use the slightly unusual WITHIN GROUP (ORDER BY ...) syntax. They do not always return the same thing.

Let's run the continuous one first, at the 50th and 90th percentiles.

PERCENTILE_CONT

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY) AS P50_CONT,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY SALARY) AS P90_CONT FROM EMPLOYEES;

Result1 row · 2 columns

P50_CONT	P90_CONT
96500.00000	121900.00000

P50_CONT is 96500.00000, which is the same value MEDIAN returned at the top of this page. That is what we would hope for. Now hold on to P90_CONT, 121900.00000, and compare it with the next grid.

PERCENTILE_DISC

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SALARY) AS P50_DISC,
       PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY SALARY) AS P90_DISC FROM EMPLOYEES;

Result1 row · 2 columns

P50_DISC	P90_DISC
96500.00	131000.00

At the 50th percentile the two functions agree. 96500.00. At the 90th they do not.

	PERCENTILE_CONT	PERCENTILE_DISC
50th percentile	96500.00000	96500.00
90th percentile	121900.00000	131000.00

And here is the whole distinction. 131000.00 is Daniel's salary. It is a value that really exists in the table, and you can find it in the INSERT at the top of this page. 121900.00000 is nobody's salary. It is interpolated between two of them.

• PERCENTILE_DISC gives us a value from the data.
• PERCENTILE_CONT gives us a value on the scale.

Which one we want depends entirely on whether “the 90th percentile salary” has to be a salary somebody is actually paid. For a pay-band review it probably does. For a latency target it probably does not.

Watch out

This is not a question you can leave to the default, because there is no default. You pick a function name, and the name is the decision. Two reports built on the same column can quote different 90th percentiles and both be correct.

Now let's run the same three on a wider column

Eight salaries is a readable example and a narrow one. It is worth running the same three statistics once on a column with more spread in it, so let's use AMOUNT on ORDERS.

Query

SELECT MEDIAN(AMOUNT) AS MED_AMOUNT, AVG(AMOUNT) AS AVG_AMOUNT, STDDEV(AMOUNT) AS SD_AMOUNT FROM ORDERS;

Result1 row · 3 columns

MED_AMOUNT	AVG_AMOUNT	SD_AMOUNT
4890.00000	5135.67500000	3542.546049137034

MED_AMOUNT is 4890.00000 and AVG_AMOUNT is 5135.67500000. The median is below the mean again, the same way round as it was on salaries.

The interesting column is the third one. SD_AMOUNT is 3542.546049137034, which is a large fraction of the mean beside it. That is a spread wide enough that quoting the average on its own would mislead whoever read it.

Seen it before

AVG_AMOUNT here is the same 5135.67500000 that episode 14 used to show that AVG divides by COUNT(column) and not COUNT(*).

One last thing: CORR and COVAR, and a warning

CORR and COVAR_SAMP exist in Snowflake and take two columns instead of one. Here is the call signature. This is also the one result in this post that you should read and then deliberately ignore.

Query — syntax only

SELECT CORR(SALARY, EMP_ID) AS CORRELATION, COVAR_SAMP(SALARY, EMP_ID) AS COVARIANCE FROM EMPLOYEES;

Result1 row · 2 columns

CORRELATION	COVARIANCE
-0.34439550692147686	-15517.857142857143

Read this before you read the grid

This number is not a finding. The query correlates SALARY against EMP_ID, and EMP_ID is an arbitrary row identifier. -0.34439550692147686 tells you nothing whatsoever about salaries at this company, because there is nothing here for a salary to be correlated with.

It is on the page for two reasons. One is the syntax, which is genuinely useful to have seen. The other is the warning. Correlating a measure against an ID column, a row number or a load timestamp is a classic way to fool ourselves, and the fact that a database will happily compute a number does not make that number a result.

If you use CORR for real, the two columns need to be things where a relationship would mean something, and you need enough rows for the answer to be worth computing at all. Eight is not enough.

Key points

• MEDIAN is the middle value and AVG is the balance point. On this table the mean is the higher of the two, because one large salary pulls it above the middle person.
• When the median and the mean disagree, the gap describes the shape of the data. Quoting only the mean hides that.
• MODE is the most common value, and this data contains no tie, so nothing here tells you how ties are resolved.
• STDDEV and VARIANCE are the sample versions and divide by n − 1. STDDEV_POP and VAR_POP divide by n. That is the whole difference.
• Standard deviation is the square root of variance, and the captured values let you check that with a calculator rather than assume it.
• A group of one row has no sample standard deviation. n − 1 is zero, so the answer is NULL. Nothing to do with NULLs in the data.
• PERCENTILE_DISC returns a value that exists in the data. PERCENTILE_CONT interpolates and can return a value nobody has.
• At the 50th percentile the two percentile functions agreed here. At the 90th they did not, so the choice between them is a real decision rather than a style preference.
• CORR against an ID column computes cleanly and means nothing. A number is not a result.

Next in this series

In the next post we will look at the approximate aggregates. APPROX_COUNT_DISTINCT, HLL and friends, which trade an exact answer for a cheaper one. We will also be honest about why a table this size cannot show you that trade at all.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES and ORDERS data is invented demo data created by that script. The one figure on this page that no query returned is the product of a captured variance and an integer, and the page says so where it appears. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake aggregate function documentation.