Snowflake SQL Tutorial 58 - Top-N Per Group in Snowflake

Snowflake SQL · Episode 58

In this post we are going to cover top-N per group in Snowflake.

Top-N per group is the question who are the two highest-paid people in each department? Or the two biggest orders for each customer. We are not asking for the top two rows in the table. We are asking for the top two in every group, all in one result. It is the window-function pattern you will write more than any other.

We will start by working out what a correct answer looks like, then write the pattern in three steps. After that we will try RANK and DENSE_RANK in the same slot, shrink the whole thing onto one line with QUALIFY, and run it on orders, where one NULL puts the wrong row at number one without changing the row count at all. This post closes Level 5.

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

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

On this page

1. Let's create some sample data
2. Let's number the rows, then filter on the number
3. Can we use RANK or DENSE_RANK instead?
4. Now let's write the whole pattern on one line with QUALIFY
5. Now let's try it on orders, and watch it go wrong
6. Let's check for ties before we trust any of it
7. How did we do top 1 before window functions?
8. That closes Level 5

Let's create some sample data

We start on EMPLOYEES, the table from Episode 1. Run this once if you have not already.

Sample data — EMPLOYEES

-- EMPLOYEES comes from Episode 1. Every episode in this series reuses the
-- same tables, so you only set them up 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
);

-- Marcus and Priya share a SALARY, but they sit in different departments.
-- That detail decides everything in the third section of this page.
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');

Our question is who are the two highest-paid people in each department? Before we write anything, let's work out what a correct answer looks like. This pattern has a failure mode that returns a perfectly plausible number of rows, so knowing the right count in advance is worth the minute it takes.

Query — the whole table, grouped by eye

SELECT DEPT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, SALARY DESC, FIRST_NAME;

Result8 rows · 3 columns

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

Department 10 has three people, 20 has two, 30 has two, and Grace has no department at all. Let's count that rather than eyeball it.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT FROM EMPLOYEES
GROUP BY DEPT_ID ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 2 columns

DEPT_ID	HEADCOUNT
10	3
20	2
30	2
NULL	1

Take the top two from each of those groups and we cannot get more than two out of any one of them. 2 from department 10, 2 from 20, 2 from 30, and 1 for Grace, because her group only has one row in it. So the answer is seven rows. Hold that number.

NULL is a group

Grace’s DEPT_ID is NULL, and that still counts as a group. PARTITION BY puts all the NULLs together into one partition rather than discarding them. So she gets a top-two list of her own, with one row in it.

Let's number the rows, then filter on the number

Every top-N-per-group query in every database is the same three steps. Partition the rows into groups, order them inside each group, then filter on the number that ordering produced.

1. PARTITION BY the column that defines a group.
2. ORDER BY the column that defines best, inside the OVER clause.
3. Keep the rows whose number is small enough.

The third step is the one that needs care, because we cannot write WHERE ROW_NUMBER() OVER (...) <= 2. WHERE runs before window functions exist. So the window function has to be computed one level down and filtered one level up.

Query

SELECT DEPT_ID, FIRST_NAME, SALARY, RN
FROM (SELECT DEPT_ID, FIRST_NAME, SALARY,
             ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) AS RN
      FROM EMPLOYEES) R
WHERE RN <= 2
ORDER BY DEPT_ID NULLS LAST, RN;

Result7 rows · 4 columns

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

Seven rows, exactly as we predicted. Daniel and Aisha from department 10. Lena and Tomas from 20. Priya and Sofia from 30. And Grace on her own, because a partition of one still has a first row.

Now look at the second thing in the window’s ORDER BY: SALARY DESC, EMP_ID. The salary is what we are ranking by. EMP_ID is a tiebreaker. It is there so that two people on the same salary get a stable, repeatable order, rather than whichever one the engine happened to reach first.

Why the tiebreaker is not optional

Always give the window an unambiguous ordering. Without a tiebreaker, a tie inside a partition means ROW_NUMBER picks a winner arbitrarily, and the same query can return a different row tomorrow on the same data. The grid will not look wrong. It will just be a different answer.

Can we use RANK or DENSE_RANK instead?

ROW_NUMBER is not the only function that can go in the ranking slot. RANK and DENSE_RANK fit there too. Let's ask the same question with each of them and see what comes back.

Query — with RANK

SELECT DEPT_ID, FIRST_NAME, SALARY, RNK
FROM (SELECT DEPT_ID, FIRST_NAME, SALARY,
             RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RNK
      FROM EMPLOYEES) R
WHERE RNK <= 2
ORDER BY DEPT_ID NULLS LAST, RNK, FIRST_NAME;

Result7 rows · 4 columns

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

Query — with DENSE_RANK

SELECT DEPT_ID, FIRST_NAME, SALARY, DR
FROM (SELECT DEPT_ID, FIRST_NAME, SALARY,
             DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DR
      FROM EMPLOYEES) R
WHERE DR <= 2
ORDER BY DEPT_ID NULLS LAST, DR, FIRST_NAME;

Result7 rows · 4 columns

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

The same seven people, all three times. Let's count all three in one query rather than compare them by eye.

Query — all three counted at once

SELECT
  (SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) N FROM EMPLOYEES) WHERE N <= 2) AS ROWS_FROM_ROW_NUMBER,
  (SELECT COUNT(*) FROM (SELECT RANK()       OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) N FROM EMPLOYEES) WHERE N <= 2) AS ROWS_FROM_RANK,
  (SELECT COUNT(*) FROM (SELECT DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) N FROM EMPLOYEES) WHERE N <= 2) AS ROWS_FROM_DENSE_RANK;

Result1 row · 3 columns

ROWS_FROM_ROW_NUMBER	ROWS_FROM_RANK	ROWS_FROM_DENSE_RANK
7	7	7

Read this before you pick one

That is a fact about this data, not a rule about these functions. They agree here because there is no tie inside any department. Marcus and Priya are the only two people in the table on the same salary, 96500.00, and they sit in departments 10 and 30. So PARTITION BY DEPT_ID separated them before the ranking ever happened. Exactly as Episode 50 showed.

So what would happen with a real tie? If two people in department 10 earned the same and were both in the top two, ROW_NUMBER would still hand us two rows and pick between them. RANK and DENSE_RANK would hand us three, because they give tied rows the same number and our <= 2 keeps all of them.

Function	With a tie inside the group, a cutoff of 2 gives you	Pick it when
ROW_NUMBER	Exactly 2 rows, always. It breaks the tie for you	A fixed row count matters more than fairness: one row per customer, a deduplication, a paging window
RANK	3 rows. Everybody who placed in the top 2	A tie should be honoured, and a gap in the numbering after it is acceptable
DENSE_RANK	3 rows. Everybody in the top 2 distinct values	You want the top n levels of a value rather than the top n rows

The choice is the same one Episode 50 framed. Exactly n rows, or everybody who tied. Nothing in this table forces it, which is exactly why it is worth deciding on purpose.

Now let's write the whole pattern on one line with QUALIFY

The subquery we wrapped the query in exists for one reason only. WHERE cannot see a window function. Snowflake has a clause that can, and Episode 23 introduced it. This is what it was for.

Query

SELECT DEPT_ID, FIRST_NAME, SALARY,
       ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) AS RN
FROM EMPLOYEES
QUALIFY RN <= 2
ORDER BY DEPT_ID NULLS LAST, RN;

Result7 rows · 4 columns

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

No subquery, no wrapping, no repeated window. QUALIFY is to window functions what HAVING is to aggregates. A filter that runs after they have been computed. And it can even refer to the alias RN defined in the same SELECT list.

Is that the same result, or just a similar one?

Worth proving rather than asserting. MINUS returns the rows in the first result that are not in the second, so if the two queries agree it returns nothing.

Query — QUALIFY version MINUS wrapped version

SELECT DEPT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) <= 2
MINUS
SELECT DEPT_ID, FIRST_NAME, SALARY
FROM (SELECT DEPT_ID, FIRST_NAME, SALARY,
             ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) AS RN
      FROM EMPLOYEES)
WHERE RN <= 2;

Result0 rows · 3 columns

An empty grid is the pass condition here: no row in either result is missing from the other.

DEPT_ID	FIRST_NAME	SALARY

Nothing came back. The two forms produce the same set of rows.

So why would we ever write the wrapped version? In Snowflake, honestly, we would not. But QUALIFY is not in every database. If the SQL has to run somewhere else as well, the subquery form is the portable one. That is the only argument for it. Nothing here is timed, and no claim is made that either one is faster.

Now let's try it on orders, and watch it go wrong

Here is the version we will actually write at work. The top two orders per customer by amount, joined to CUSTOMERS so the output carries names instead of ids.

Sample data — CUSTOMERS

-- CUSTOMERS, from the same shared sample-data script. ORDERS is created by
-- that script too, and it carries one deliberate NULL amount on the last row
-- inserted -- which is what the next section of this page is about.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE CUSTOMERS (
    CUSTOMER_ID    INT,
    CUSTOMER_NAME  VARCHAR,
    CITY           VARCHAR,
    COUNTRY        VARCHAR,
    SEGMENT        VARCHAR,
    SIGNUP_DATE    DATE
);

INSERT INTO CUSTOMERS VALUES
    (101, 'Northwind Traders', 'Charlotte',  'USA',    'Enterprise', '2022-01-15'),
    (102, 'Blue Ridge Supply', 'Asheville',  'USA',    'SMB',        '2022-04-02'),
    (103, 'Kestrel Analytics', 'Toronto',    'Canada', 'Enterprise', '2022-06-21'),
    (104, 'Harbour Foods',     'Vancouver',  'Canada', 'SMB',        '2023-02-08'),
    (105, 'Alpine Logistics',  'Zurich',     NULL,     'Enterprise', '2023-05-30'),
    (106, 'Delta Print Co',    'Raleigh',    'USA',    'SMB',        '2023-09-12'),
    (107, 'Meridian Health',   'Boston',     'USA',    'Enterprise', '2024-01-07'),
    (108, 'Copper Kettle Ltd', 'Manchester', 'UK',     'SMB',        '2024-03-25'),
    (109, 'Vantage Media',     'London',     'UK',     'Enterprise', '2024-08-14'),
    (110, 'Sable Interiors',   'Austin',     'USA',    'SMB',        '2024-11-03');

Query

SELECT C.CUSTOMER_NAME, O.ORDER_ID, O.ORDER_DATE, O.AMOUNT, O.RN
FROM (SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, AMOUNT,
             ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY AMOUNT DESC, ORDER_ID) AS RN
      FROM ORDERS) O
JOIN CUSTOMERS C ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.RN <= 2
ORDER BY C.CUSTOMER_NAME, O.RN;

Result16 rows · 5 columns

CUSTOMER_NAME	ORDER_ID	ORDER_DATE	AMOUNT	RN
Alpine Logistics	1026	2024-02-17	8340.00	1
Alpine Logistics	1007	2023-04-05	7890.00	2
Blue Ridge Supply	1028	2024-03-21	1240.00	1
Blue Ridge Supply	1008	2023-04-19	1120.00	2
Delta Print Co	1041	2024-11-05	NULL	1
Delta Print Co	1027	2024-03-04	1690.00	2
Harbour Foods	1036	2024-08-02	2540.00	1
Harbour Foods	1005	2023-03-09	2310.75	2
Kestrel Analytics	1031	2024-05-10	4025.00	1
Kestrel Analytics	1024	2024-01-15	3910.00	2
Meridian Health	1037	2024-08-25	13100.00	1
Meridian Health	1032	2024-05-28	12850.00	2
Northwind Traders	1040	2024-10-16	9640.00	1
Northwind Traders	1035	2024-07-18	8820.00	2
Vantage Media	1038	2024-09-11	5930.00	1
Vantage Media	1030	2024-04-23	5180.00	2

Sixteen rows. Two for each of the eight customers that have orders. CUSTOMERS holds ten rows, but two of them never placed an order, and an inner join drops them. That is why this is not twenty rows.

Both of those claims are checkable, so let's check them.

Query — what the row count should be

SELECT COUNT(DISTINCT CUSTOMER_ID) AS CUSTOMERS_WITH_ORDERS,
       COUNT(DISTINCT CUSTOMER_ID) * 2 AS EXPECTED_ROWS_IF_ALL_HAVE_TWO
FROM ORDERS;

Result1 row · 2 columns

CUSTOMERS_WITH_ORDERS	EXPECTED_ROWS_IF_ALL_HAVE_TWO
8	16

Query — any customer with fewer than two orders?

SELECT CUSTOMER_ID, COUNT(*) AS N_ORDERS
FROM ORDERS GROUP BY CUSTOMER_ID HAVING COUNT(*) < 2 ORDER BY CUSTOMER_ID;

Result0 rows · 2 columns

Empty again, and again that is the answer: every customer with orders has at least two, so no partition returned a short list.

CUSTOMER_ID	N_ORDERS

The arithmetic holds and the grid is the right size. Now read the Delta Print Co rows.

Delta Print Co's RN 1 row is not their biggest order

Delta Print Co’s number-one order is 1041, and its AMOUNT is NULL. Their real largest order is the one at RN 2, which is 1690.00. The row count is right and the answer is wrong.

This is the behaviour Episode 7 covered. In Snowflake, ORDER BY ... DESC puts NULLs first by default. A NULL is treated as larger than any value when we sort descending. Inside a window, that means an order with no amount recorded sorts straight to the top of its customer’s ranking, and takes the number-one slot off the order that deserves it.

The fix is one clause, and it goes inside the window’s own ORDER BY. Not in the outer ORDER BY, which only controls how the final grid is displayed and has no effect on the numbering at all.

The fix — NULLS LAST inside the window

-- The same query, with the NULL ordering stated explicitly INSIDE the window.
SELECT C.CUSTOMER_NAME, O.ORDER_ID, O.ORDER_DATE, O.AMOUNT, O.RN
FROM (
    SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, AMOUNT,
           ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID
                              ORDER BY AMOUNT DESC NULLS LAST, ORDER_ID) AS RN
    FROM ORDERS
) O
JOIN CUSTOMERS C ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.RN <= 2
ORDER BY C.CUSTOMER_NAME, O.RN;

Why this one is dangerous

Nothing about the broken query looks wrong. It compiles, it returns the expected number of rows, the join is right and the ranking function is right. It is only wrong once you read the data. So check what your ORDER BY does with NULLs every single time you write this pattern. Or write NULLS LAST by reflex and never think about it again.

Let's check for ties before we trust any of it

Earlier we found that ROW_NUMBER, RANK and DENSE_RANK agreed on EMPLOYEES, because no tie sat inside a partition. The orders query partitions by something else entirely and orders by AMOUNT, so we have to ask the same question again. It is one query to ask it.

Query — two orders at the same amount, for the same customer?

SELECT CUSTOMER_ID, AMOUNT, COUNT(*) AS ORDERS_AT_THIS_AMOUNT
FROM ORDERS
GROUP BY CUSTOMER_ID, AMOUNT
HAVING COUNT(*) > 1
ORDER BY CUSTOMER_ID, AMOUNT;

Result0 rows · 3 columns

No rows, so no customer has two orders at an identical amount. The three ranking functions cannot disagree on this data either.

CUSTOMER_ID	AMOUNT	ORDERS_AT_THIS_AMOUNT

That is a cheap habit worth keeping. The check is a GROUP BY on the partition key and the ordering key with HAVING COUNT(*) > 1. The answer tells us whether our choice of ranking function can change the result at all.

How did we do top 1 before window functions?

One comparison to finish. The highest-paid person in each department, which is top one per group, written the way we would have had to write it before this level.

Query — a correlated subquery

SELECT E.DEPT_ID, E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY = (SELECT MAX(E2.SALARY) FROM EMPLOYEES E2
                  WHERE E2.DEPT_ID = E.DEPT_ID
                     OR (E2.DEPT_ID IS NULL AND E.DEPT_ID IS NULL))
ORDER BY E.DEPT_ID NULLS LAST;

Result4 rows · 3 columns

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

Four rows, and it is correct. But look at what the WHERE clause had to grow to get there: E2.DEPT_ID = E.DEPT_ID OR (E2.DEPT_ID IS NULL AND E.DEPT_ID IS NULL). That second half exists entirely for Grace, because NULL = NULL is not true, and without it her department never matches itself.

Now the same question with QUALIFY and ROW_NUMBER.

Query — QUALIFY and ROW_NUMBER

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

Result4 rows · 3 columns

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

The same four people, from one line of filtering, with no special case for the NULL department. PARTITION BY groups NULLs together instead of comparing them.

Query — correlated version MINUS window version

SELECT E.DEPT_ID, E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY = (SELECT MAX(E2.SALARY) FROM EMPLOYEES E2
                  WHERE E2.DEPT_ID = E.DEPT_ID
                     OR (E2.DEPT_ID IS NULL AND E.DEPT_ID IS NULL))
MINUS
SELECT DEPT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC, EMP_ID) = 1;

Result0 rows · 3 columns

Empty, so the two really do return the same set.

DEPT_ID	FIRST_NAME	SALARY

Both are right. The window version says what it means on one line, needs no correlated scan of the table against itself, and handles the NULL group without being told about it. It also scales to top-n by changing = 1 to <= n, which the correlated form does not do gracefully at all.

Do not just delete it. Recognise it

The correlated query is still worth being able to read. It turns up constantly in older code, and recognising it as top-1 per group, written the long way is how you know it is safe to replace.

That closes Level 5

This post ends the window-function level. Everything in it was built on one idea. A window function computes a value for a row using other rows, without collapsing them the way GROUP BY does.

What the level covered	What it gave you
OVER, PARTITION BY, ORDER BY inside OVER	The window itself: which rows, in which order
ROW_NUMBER, RANK, DENSE_RANK, NTILE	Position, placing, and buckets
PERCENT_RANK, CUME_DIST, RATIO_TO_REPORT	Where a row sits in a distribution, and its share of a total
LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE	Reading another row's value from this row
Frames, ROWS versus RANGE, moving averages	Narrowing the window to a span around the current row
QUALIFY and top-N per group	Filtering on what a window function produced

And of that whole list, the pattern on this page is the one you will type most. Partition, order, filter on the number. Then check what the ordering did with your NULLs before you believe the top row.

Key points

• Top-N per group is three steps: PARTITION BY the group, ORDER BY what makes a row best, then filter on the number that ordering produced.
• WHERE cannot see a window function, so either wrap the query in a subquery or use QUALIFY. They return the same set, proved on this page with MINUS rather than asserted.
• QUALIFY is the Snowflake-native form and the one to write by default. The subquery form is the portable one, for SQL that has to run elsewhere too.
• ROW_NUMBER gives you exactly n rows and breaks ties itself. RANK and DENSE_RANK keep everybody who tied, so they can return more rows than you asked for.
• All three agreed on this data because no tie sat inside a partition. That is a property of the table, not of the functions. One GROUP BY ... HAVING COUNT(*) > 1 tells you whether it holds for yours.
• Always give the window an unambiguous ORDER BY, tiebreaker included, or the same query can pick a different winner on the same data.
• ORDER BY ... DESC puts NULLs first in Snowflake, so a missing value can take the number-one slot inside a window without changing the row count. Write DESC NULLS LAST inside the window.
• The outer ORDER BY controls display only. The ranking is decided by the ORDER BY inside OVER, and fixing the wrong one fixes nothing.
• A correlated subquery needs an explicit IS NULL special case for a NULL group key. PARTITION BY handles it without being asked.

Next in this series

In the next post we start Level 6 with PIVOT. We will turn one column’s values into column names in four lines of SQL, and we will see the single omission that turns an eight-row answer into a forty-one-row one.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EMPLOYEES, CUSTOMERS and ORDERS data is invented demo data created by that script, and the amounts carry no currency. No performance or benchmark claims are made anywhere in this series — where two queries are compared here, they are compared for what they return and how they read, never for speed. Clause behaviour follows the Snowflake QUALIFY documentation, the ROW_NUMBER documentation and the ORDER BY documentation.