Snowflake SQL Tutorial 23 - QUALIFY in Snowflake

Snowflake SQL · Episode 23

In this post we are going to cover QUALIFY in Snowflake. It is the last post of Level 2.

QUALIFY is a filter. It filters on the result of a window function, in the same way that WHERE filters rows and HAVING filters groups. That is what turns “the top row in each group” into a single clause instead of a subquery.

We will look at a window function first, before we filter on one. Then we will write the QUALIFY version, then the subquery people wrote before the clause existed, then the three filters of this level side by side. At the end we will find a query that runs, returns the right number of rows, and quietly picks the wrong ones.

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

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

On this page

1. Let's create some sample data
2. Let's look at a window function before we filter on one
3. Now let's filter on it with QUALIFY
4. Now let's write what people wrote before QUALIFY
5. Now let's try a different window function
6. Now let's use one that is not a ranking at all
7. Now let's put WHERE and QUALIFY in the same query
8. Let's line up all three filters of this level
9. Now let's write the shape you will actually use
10. Let's chase it down: why did a NULL win?
11. Now let's fix it with NULLS LAST

Let's create some sample data

Everything in the first half of this page runs against EMPLOYEES. It is the table from episode 1, and it comes from the same shared sample-data script.

Copy this into a Snowflake worksheet and run it once, if you have not already. Then you can run every query on this page yourself.

Setup — run once

-- Creates the EMPLOYEES table used by every episode in this series.
-- Part of the shared sample-data script; run it once.
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');

Eight employees, each with a department ID and a salary. One of them matters more than the rest. Grace has no department at all, and we will come back to her more than once.

Let's look at a window function before we filter on one

QUALIFY cannot be shown without a window function, and window functions get a whole level of their own later in this course. So we are going to use one before it has been taught, and keep it simple. There is exactly one idea we need from it today.

Let's select the window function as an ordinary visible column, so we can see what it computed before anything filters on it. Read the OVER clause as a sentence: number the rows, starting again for each department, highest salary first.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY,
       ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN
FROM EMPLOYEES ORDER BY DEPT_ID, RN;

Result8 rows · 4 columns

FIRST_NAME	DEPT_ID	SALARY	RN
Daniel	10	131000.00	1
Aisha	10	118000.00	2
Marcus	10	96500.00	3
Lena	20	104000.00	1
Tomas	20	87250.00	2
Priya	30	96500.00	1
Sofia	30	71400.00	2
Grace	NULL	92000.00	1

Eight rows came back, which is the whole table. Nothing has been filtered. A window function adds a column. It never removes a row.

Now read the RN column downwards. Daniel is 1, Aisha is 2, Marcus is 3. Then Lena is 1 again, because department 20 has started and the numbering restarted.

That restart is what PARTITION BY does, and it is the only thing we need from window functions on this page. The top earner in each department is the row where RN is 1, and there are four of those in that grid.

Now let's filter on it with QUALIFY

So we want those four rows and nothing else. The instinct is to put the condition in a WHERE clause. That does not work. At the moment WHERE runs, the row number does not exist yet. QUALIFY is the clause that runs late enough to use it.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES
QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) = 1
ORDER BY DEPT_ID;

Result4 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Daniel	10	131000.00
Lena	20	104000.00
Priya	30	96500.00
Grace	NULL	92000.00

Four rows: Daniel, Lena, Priya and Grace. The top earner in each department, including the NULL one, which is a group like any other here.

Now look at what is not in that output. The RN column is gone. We filtered on the row number without ever selecting it, exactly the way episode 17's HAVING filtered on a SUM it was not displaying. We do not have to show a value in order to filter on it.

Where it goes in the statement

QUALIFY goes after WHERE and GROUP BY / HAVING, and before ORDER BY and LIMIT. If you are writing it from memory, that is the position to remember. It is the last filter in the statement.

Now let's write what people wrote before QUALIFY

The obvious question is what we did about this before the clause existed, and what we still do in a database that does not have it. We compute the row number in a subquery, which gives it a name, and then filter on that name from the outside with an ordinary WHERE.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM (
  SELECT FIRST_NAME, DEPT_ID, SALARY,
         ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN
  FROM EMPLOYEES
) WHERE RN = 1 ORDER BY DEPT_ID;

Result4 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Daniel	10	131000.00
Lena	20	104000.00
Priya	30	96500.00
Grace	NULL	92000.00

The same four rows. Daniel, Lena, Priya, Grace.

That is QUALIFY's entire pitch: the same query with the scaffolding removed. No subquery, and no column that only exists so it can be thrown away one level up.

What this comparison does and does not prove

Being careful about what has just been shown: these two queries returned the same rows on this table. That is one pair, compared once. It is not a proof of general equivalence and this page is not going to pretend it is.
And the argument for QUALIFY is readability, not speed. Nothing on this page was timed, so no performance claim is made in either direction.

Now let's try a different window function

QUALIFY is not tied to ROW_NUMBER. Let's use RANK instead, with no PARTITION BY at all, which makes the window the whole table. We will keep everybody ranked third or better.

Query

SELECT FIRST_NAME, SALARY FROM EMPLOYEES QUALIFY RANK() OVER (ORDER BY SALARY DESC) <= 3 ORDER BY SALARY DESC;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Daniel	131000.00
Aisha	118000.00
Lena	104000.00

The top three salaries overall: Daniel, Aisha, Lena. Dropping PARTITION BY did not break anything. It just means there is one window over everything instead of one per department.

RANK or ROW_NUMBER

This example uses RANK rather than ROW_NUMBER, and the two behave differently when values tie, which matters a great deal for a query shaped like this one. That difference belongs to the window-functions level and is not demonstrated here, so this page does not tell you what it is.

Now let's use one that is not a ranking at all

Rankings are the common case, not the rule. QUALIFY takes any expression that involves a window function. Let's write one with no ranking in it, and keep everybody paid above the average for their own department.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES
QUALIFY SALARY > AVG(SALARY) OVER (PARTITION BY DEPT_ID)
ORDER BY DEPT_ID, SALARY DESC;

Result4 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Daniel	10	131000.00
Aisha	10	118000.00
Lena	20	104000.00
Priya	30	96500.00

Four people are paid above their own department's average. AVG(SALARY) OVER (PARTITION BY DEPT_ID) is an aggregate used as a window function. It computes one average per department and then makes it available on every row of that department, which is what lets QUALIFY compare each salary against it.

Why Grace is not in that list

Grace is the only person in the NULL department, so her department's average is her own salary. 92000.00 is not greater than 92000.00, so she is excluded. The comparison is strict.

The edge that catches people

A one-row group can never beat its own average. That is obvious once stated and very easy to miss on real data, where a filter like this one quietly drops every group of one. And groups of one are exactly what a long tail is made of. If you want them, you have to say >= and mean it.

Now let's put WHERE and QUALIFY in the same query

WHERE and QUALIFY are not alternatives, in exactly the way WHERE and HAVING were not alternatives in episode 17. Let's use both in one statement. Drop the NULL department first, then take the top earner in each of the ones that are left.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES
WHERE DEPT_ID IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) = 1
ORDER BY DEPT_ID;

Result3 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Daniel	10	131000.00
Lena	20	104000.00
Priya	30	96500.00

Three rows instead of four. Grace is gone, and the important part is when she went. WHERE removed her before the window ever ran, so her row never got a row number at all.

That ordering is worth holding on to, because it is also how we would get the opposite answer. A condition in QUALIFY filters rows that the window has already numbered. A condition in WHERE changes what the window sees. Move a predicate between the two clauses and we are not reorganising a query. We are asking a different question.

Let's line up all three filters of this level

This is the picture worth keeping from the whole of this level, and it is the reason QUALIFY is taught here rather than with the window functions.

Clause	Filters	Runs
WHERE	rows	before any grouping
HAVING	groups	after grouping
QUALIFY	the result of a window function	after the window has been computed

All three are filters. They run at different moments, and each one exists because the thing it filters on does not exist yet when the clause before it runs. We cannot put a COUNT in a WHERE clause because there are no groups yet. We cannot put a ROW_NUMBER in one either, for exactly the same reason, one step further along.

Learn the order the clauses run in and all three stop being rules you memorise. They start being things you can work out from first principles, including the error message you get when you put one in the wrong place.

Now let's write the shape you will actually use

One row per group, picked by a ranking, is the most common real use of QUALIFY by a wide margin. The classic version of it is “the biggest order per customer”, so the rest of this page moves to the ORDERS table.

Setup — the ORDERS table

-- Creates the ORDERS table that Level 2 works on.
-- Part of the shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID     INT,
    CUSTOMER_ID  INT,
    ORDER_DATE   DATE,
    SHIP_DATE    DATE,
    STATUS       VARCHAR,
    AMOUNT       NUMBER(10,2),
    QUANTITY     INT
);

INSERT INTO ORDERS VALUES
    (1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED',   4200.00,  12),
    (1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED',    890.50,   3),
    (1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED',   6150.00,  20),
    (1004, 103, '2023-02-27', NULL,         'CANCELLED', 1500.00,   5),
    (1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED',   2310.75,   8),
    (1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED',   5020.00,  16),
    (1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED',   7890.00,  25),
    (1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED',   1120.00,   4),
    (1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED',   3340.25,  11),
    (1010, 106, '2023-05-21', NULL,         'PENDING',    640.00,   2),
    (1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED',   8100.00,  27),
    (1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED',   1975.50,   7),
    (1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED',   6420.00,  21),
    (1014, 102, '2023-07-29', '2023-08-03', 'RETURNED',   750.00,   3),
    (1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED',   9250.00,  30),
    (1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED',   2860.00,   9),
    (1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED',   5540.00,  18),
    (1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED',   1430.00,   5),
    (1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED',   7015.00,  23),
    (1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED',  10400.00,  34),
    (1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED',    980.00,   4),
    (1022, 104, '2023-12-09', NULL,         'CANCELLED', 2250.00,   8),
    (1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED',   6730.00,  22),
    (1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED',   3910.00,  13),
    (1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED',  11200.00,  36),
    (1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED',   8340.00,  27),
    (1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED',   1690.00,   6),
    (1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED',   1240.00,   5),
    (1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED',   7460.00,  24),
    (1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED',   5180.00,  17),
    (1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED',   4025.00,  14),
    (1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED',  12850.00,  41),
    (1033, 105, '2024-06-13', NULL,         'PENDING',   6900.00,  22),
    (1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED',   4760.00,  16),
    (1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED',   8820.00,  29),
    (1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED',   2540.00,   9),
    (1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED',  13100.00,  43),
    (1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED',   5930.00,  19),
    (1039, 102, '2024-09-29', '2024-10-03', 'RETURNED',  1080.00,   4),
    (1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED',   9640.00,  31);

-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there: it exists so that COUNT(*), COUNT(AMOUNT) and SUM() give
-- different answers.
INSERT INTO ORDERS VALUES
    (1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);

Let's ask for the biggest order each customer has placed.

Query

SELECT CUSTOMER_ID, ORDER_ID, AMOUNT FROM ORDERS
QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY AMOUNT DESC) = 1
ORDER BY CUSTOMER_ID;

Result8 rows · 3 columns

CUSTOMER_ID	ORDER_ID	AMOUNT
101	1040	9640.00
102	1028	1240.00
103	1031	4025.00
104	1036	2540.00
105	1026	8340.00
106	1041	NULL
107	1037	13100.00
109	1038	5930.00

Eight rows, one per customer. The query is three lines and there is no subquery in sight, which is the whole reason people reach for this clause.

Look at one row before you trust this

Now look at customer 106. Their “biggest” order is 1041, and its AMOUNT is NULL. Nothing errored. Nothing looks wrong. The answer is simply not the one that was asked for.

That is worth chasing down rather than shrugging at, and the next two queries do exactly that.

Let's chase it down: why did a NULL win?

First, let's look at every order that customer has, because the cheapest explanation would be that they only ever placed the one.

Query

SELECT CUSTOMER_ID, ORDER_ID, AMOUNT
FROM ORDERS
WHERE CUSTOMER_ID = 106
ORDER BY AMOUNT DESC;

Result4 rows · 3 columns

CUSTOMER_ID	ORDER_ID	AMOUNT
106	1041	NULL
106	1027	1690.00
106	1018	1430.00
106	1010	640.00

Four orders, so that is not it. Three of them have a real amount, and the largest of those is 1690.00.

Now let's run the same rows with the window's own ROW_NUMBER shown as a column, so the ordering inside the window is visible rather than inferred.

Query

SELECT CUSTOMER_ID, ORDER_ID, AMOUNT,
       ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY AMOUNT DESC) AS RN
FROM ORDERS
WHERE CUSTOMER_ID = 106
ORDER BY RN;

Result4 rows · 4 columns

CUSTOMER_ID	ORDER_ID	AMOUNT	RN
106	1041	NULL	1
106	1027	1690.00	2
106	1018	1430.00	3
106	1010	640.00	4

There it is. ORDER BY AMOUNT DESC put the NULL at the top, ahead of 1690.00. So the window function ranked a row with no amount as this customer's biggest order, and QUALIFY dutifully kept it. Every clause did exactly what it was told.

You have seen this before

This is episode 7 all over again, in a place you would never think to look for it. Back there we sorted a result set and saw where NULLs land under DESC. Here the same ordering is happening inside a window function, where there is no visible sort in the output to remind you that a sort happened at all.

Now let's fix it with NULLS LAST

One clause fixes it, and the only thing that matters is where we put it. NULLS LAST goes inside the window's own ORDER BY.

Query

SELECT CUSTOMER_ID, ORDER_ID, AMOUNT
FROM ORDERS
QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY AMOUNT DESC NULLS LAST) = 1
ORDER BY CUSTOMER_ID;

Result8 rows · 3 columns

CUSTOMER_ID	ORDER_ID	AMOUNT
101	1040	9640.00
102	1028	1240.00
103	1031	4025.00
104	1036	2540.00
105	1026	8340.00
106	1027	1690.00
107	1037	13100.00
109	1038	5930.00

Customer 106's top order is now 1027 at 1690.00, a real order with a real amount. Every other row is unchanged, because no other customer had a NULL in the way. Compare it against the grid two sections up: one row differs, and it is the row that was wrong.

The same words in the wrong place do nothing

Putting NULLS LAST on the statement's own ORDER BY instead would change the order of the rows you get back, and do nothing whatsoever about which row won. The window's ORDER BY and the query's ORDER BY are two different sorts that happen at two different times, and only one of them decides the answer.

The rule worth taking away: if the column we are ranking on is nullable, say where the NULLs go. Be explicit inside every window ORDER BY, the same way you would be in a top-level one. This is the failure mode that does not announce itself. A query that runs, returns the right number of rows, and quietly picked the wrong ones.

Key points

• QUALIFY filters on the result of a window function, which a WHERE clause cannot do because the window has not run yet when WHERE does.
• You can filter on a window function without selecting it — the ROW_NUMBER column does not have to appear in the output.
• WHERE filters rows before grouping, HAVING filters groups after grouping, QUALIFY filters after the window is computed. Each exists because the thing it filters on does not exist yet one clause earlier.
• The pattern QUALIFY replaces is a subquery that computes a row number and an outer WHERE that filters on it. Both returned the same rows on this table.
• QUALIFY accepts any expression involving a window function, including an aggregate window like AVG(...) OVER (PARTITION BY ...).
• A one-row group can never be above its own average, because the comparison is strict.
• WHERE and QUALIFY in the same query are not redundant: WHERE changes what the window sees, QUALIFY filters what it produced.
• If the column a window ranks on can be NULL, say where the NULLs go. Under DESC a NULL can win a ranking and nothing will error.
• NULLS LAST belongs inside the window's ORDER BY. On the statement's ORDER BY it changes the output order and nothing else.

Next in this series

Episode 24 — INNER JOIN. That is the end of Level 2 on aggregation, from COUNT all the way to here. The next level is joins, and it starts with the one you will write most often.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the scripts above. The EMPLOYEES and ORDERS data is invented demo data created by those scripts. The QUALIFY and subquery forms shown side by side returned the same rows on this table, which is one comparison and not a proof of general equivalence. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake QUALIFY documentation.