Snowflake SQL Tutorial 50 - RANK vs DENSE_RANK in Snowflake

Snowflake SQL · Episode 50

In this post we are going to cover RANK and DENSE_RANK in Snowflake.

Both of them rank the rows in an order we choose, and both of them give two equal rows the same number. That is what makes them rankings rather than numberings. They agree on everything except one thing: what the number after a tie should be.

We will run both of them beside ROW_NUMBER so the difference is on one screen, then read each column on its own, work out why each one gives the answer it gives, and finish with what “give me the top three” actually means to each of them.

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

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

On this page
  1. Let's create some sample data
  2. Let's run all three functions in one query
  3. Now let's read the two columns one at a time
  4. Can we ask Snowflake which number went missing?
  5. Now let's count the numbers each function handed out
  6. Now let's ask all three for the top three
  7. Can all three take a partition?
  8. Let's try it on a bigger table

Let's create some sample data

These two functions behave identically on a table with no duplicate values in the ordering column. Everything that tells them apart needs a tie, so we need a table that has one. Paste this into a worksheet and run it once.

Sample data
-- EMPLOYEES comes from Episode 1 and ORDERS from Level 3. 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 have the same SALARY. That one duplicated value is the
-- entire subject of this episode.
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');

Now let's show that the tie is really there rather than take it on trust.

Query — which salaries are shared, and by whom
SELECT SALARY, COUNT(*) AS PEOPLE,
       LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS WHO
FROM EMPLOYEES
GROUP BY SALARY
HAVING COUNT(*) > 1;
Result1 row · 3 columns
SALARYPEOPLEWHO
96500.002Marcus, Priya

One row back, so there is exactly one shared salary in EMPLOYEES: Marcus and Priya. Episode 49 showed ROW_NUMBER splitting that pair into two different numbers without being asked to. RANK and DENSE_RANK both refuse to do that, in two different ways.

Let's run all three functions in one query

The fastest way to see what separates them is to run all three over the same window specification, in the same SELECT. Same ORDER BY SALARY DESC for each one. The only difference is the function name.

Query
SELECT FIRST_NAME, SALARY,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN,
       RANK()       OVER (ORDER BY SALARY DESC) AS RNK,
       DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE
FROM EMPLOYEES
ORDER BY SALARY DESC, FIRST_NAME;
Result8 rows · 5 columns
FIRST_NAMESALARYRNRNKDENSE
Daniel131000.00111
Aisha118000.00222
Lena104000.00333
Marcus96500.00444
Priya96500.00544
Grace92000.00665
Tomas87250.00776
Sofia71400.00887

The first four rows are unanimous. Then two things happen, one row apart.

  • At Priya, the second half of the tie, RN moves on while RNK and DENSE repeat the number Marcus already has. That is what “ranking” means: equal values get equal numbers.
  • At Grace, the row after the tie, RNK and DENSE stop agreeing with each other. RNK says 6. DENSE says 5.

That one cell is the whole difference between RANK and DENSE_RANK. Every row below it inherits the same gap, and the rest of this post is about why.

Now let's read the two columns one at a time

Let's run the same two functions again, one per query, so we can read each sequence straight down without the other columns in the way. RANK first.

Query — RANK
SELECT RANK() OVER (ORDER BY SALARY DESC) AS RNK, FIRST_NAME, SALARY
FROM EMPLOYEES ORDER BY RNK, FIRST_NAME;
Result8 rows · 3 columns
RNKFIRST_NAMESALARY
1Daniel131000.00
2Aisha118000.00
3Lena104000.00
4Marcus96500.00
4Priya96500.00
6Grace92000.00
7Tomas87250.00
8Sofia71400.00

Now DENSE_RANK, with nothing changed but the function name.

Query — DENSE_RANK
SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE, FIRST_NAME, SALARY
FROM EMPLOYEES ORDER BY DENSE, FIRST_NAME;
Result8 rows · 3 columns
DENSEFIRST_NAMESALARY
1Daniel131000.00
2Aisha118000.00
3Lena104000.00
4Marcus96500.00
4Priya96500.00
5Grace92000.00
6Tomas87250.00
7Sofia71400.00

Read the two grids as a pair, top to bottom. They are identical for the first five rows, including the tied pair, which both of them give the same number. They first disagree on Grace’s row, and never agree again. RANK puts her on 6 and DENSE_RANK puts her on 5.

Why RANK gives her 6

RANK counts rows. Your rank is one plus the number of rows strictly ahead of you. Count the rows above Grace: Daniel, Aisha, Lena, Marcus and Priya all out-earn her. Five rows ahead, so her rank is 6.

The tie did not change that arithmetic. Marcus and Priya are still two rows, and they are still both ahead of her. What the tie changed is that they share a number, so one number in the sequence has nobody to hold it.

Why DENSE_RANK gives her 5

DENSE_RANK counts distinct values. Your dense rank is one plus the number of distinct values ahead of yours. Above Grace’s salary there are three higher salaries and the shared one, which is four distinct values, so her dense rank is 5. Two people sharing a value use up one number between them, and that is exactly what “dense” means: no holes.

ROW_NUMBERRANKDENSE_RANK
Tied rows getdifferent numbersthe same numberthe same number
Countsrowsrowsdistinct values
After a tiecontinuesskipscontinues
Gaps in the sequenceneverafter every tienever
Highest number equalsthe row countthe row count, unless the lowest value tiesthe number of distinct values

Can we ask Snowflake which number went missing?

“RANK skips a number” is easy to say and easy to half-believe. So instead of asserting it, let's put the numbers one to eight in a VALUES list, the construct from Episode 45, and ask which of them the RANK column never produced.

Query
SELECT N AS MISSING_RANK
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) AS T (N)
WHERE N NOT IN (SELECT RANK() OVER (ORDER BY SALARY DESC) FROM EMPLOYEES);
Result1 row · 1 column
MISSING_RANK
5

One row, and the number in it is the one missing from the RANK result. A tie of two swallowed exactly one number.

Generalising it

The size of the hole is the size of the tie minus one. Three people on the same salary would all take the same rank and the next row would jump by three. DENSE_RANK leaves no hole at all, whatever the size of the tie.

Now let's count the numbers each function handed out

There is a tidier way to state the difference than reading down a grid. Let's count how many distinct numbers each function produced, and what the largest one was.

Query
SELECT COUNT(DISTINCT RN) AS DISTINCT_ROW_NUMBERS,
       COUNT(DISTINCT RNK) AS DISTINCT_RANKS,
       COUNT(DISTINCT DENSE) AS DISTINCT_DENSE_RANKS,
       MAX(RNK) AS MAX_RANK,
       MAX(DENSE) AS MAX_DENSE
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN,
             RANK()       OVER (ORDER BY SALARY DESC) AS RNK,
             DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE
      FROM EMPLOYEES);
Result1 row · 5 columns
DISTINCT_ROW_NUMBERSDISTINCT_RANKSDISTINCT_DENSE_RANKSMAX_RANKMAX_DENSE
87787

Read that left to right. ROW_NUMBER produced a distinct number for every row, which is its defining property. RANK and DENSE_RANK each produced the same, smaller count of distinct numbers. There are seven distinct salaries in this table, and both functions produced one number per salary level.

Then the two columns on the right, which are the point. MAX_RANK and MAX_DENSE are not the same number. Both functions handed out the same quantity of distinct values, but RANK spread them over a wider range in order to keep every row's position honest. DENSE_RANK packed them together.

  • MAX(DENSE_RANK) always equals the number of distinct values in the ordering column. That one is an identity, whatever the data looks like.
  • MAX(RANK) equals the row count when the lowest value is not shared, as it is not here — the last row’s rank is one plus every row ahead of it. Put a tie at the bottom and the largest rank lands below the row count instead.

The first of those is the most useful thing to remember about DENSE_RANK. It is a counter of levels, so its top number tells us how many levels there were.

Now let's ask all three for the top three

So which one do we want? It depends on what top n means to the person asking, and it means three different things. Let's start with a cutoff of three, where it happens not to matter at all.

Query — ROW_NUMBER, with a tiebreak
SELECT RN, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMP_ID) AS RN, FIRST_NAME, SALARY
      FROM EMPLOYEES) R
WHERE RN <= 3
ORDER BY RN;
Result3 rows · 3 columns
RNFIRST_NAMESALARY
1Daniel131000.00
2Aisha118000.00
3Lena104000.00
Query — RANK
SELECT RNK, FIRST_NAME, SALARY
FROM (SELECT RANK() OVER (ORDER BY SALARY DESC) AS RNK, FIRST_NAME, SALARY
      FROM EMPLOYEES) R
WHERE RNK <= 3
ORDER BY RNK, FIRST_NAME;
Result3 rows · 3 columns
RNKFIRST_NAMESALARY
1Daniel131000.00
2Aisha118000.00
3Lena104000.00
Query — DENSE_RANK
SELECT DENSE, FIRST_NAME, SALARY
FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE, FIRST_NAME, SALARY
      FROM EMPLOYEES) R
WHERE DENSE <= 3
ORDER BY DENSE, FIRST_NAME;
Result3 rows · 3 columns
DENSEFIRST_NAMESALARY
1Daniel131000.00
2Aisha118000.00
3Lena104000.00

Three rows each, and the same three people in all three grids. That agreement is not a property of the functions. It is a property of this data. The tie sits at 4, just below the cut, so none of the three functions has had to make a decision yet.

Now let's move the cut onto the tie

At a cutoff of four the tie is inside the window, and the three functions answer differently. Let's get the row counts of all three cutoffs in a single row.

Query
SELECT
  (SELECT COUNT(*) FROM (SELECT RANK() OVER (ORDER BY SALARY DESC) R FROM EMPLOYEES) WHERE R <= 4) AS ROWS_WITH_RANK_LE_4,
  (SELECT COUNT(*) FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) D FROM EMPLOYEES) WHERE D <= 4) AS ROWS_WITH_DENSE_LE_4,
  (SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC, EMP_ID) N FROM EMPLOYEES) WHERE N <= 4) AS ROWS_WITH_ROWNUM_LE_4;
Result1 row · 3 columns
ROWS_WITH_RANK_LE_4ROWS_WITH_DENSE_LE_4ROWS_WITH_ROWNUM_LE_4
554

ROW_NUMBER returns exactly as many rows as the cutoff, because it never ties. Ask for four and we get four. RANK and DENSE_RANK both let the whole tied pair through, so both return one more row than the cutoff.

What this grid does not prove

On this data RANK <= 4 and DENSE_RANK <= 4 return the same number of rows. That is a fact about this table, not a rule — the two functions only start returning different row counts once a tie sits above the cutoff rather than on it.

We can see why the two come apart without running anything else. A cutoff of 5 would be tested against two different columns. In the RANK result no row carries 5 at all, whereas in the DENSE_RANK result 5 is Grace’s row. Same predicate, different column, different meaning.

So which one do we reach for?

What the request actually meansFunctionWhy
“Give me exactly n rows” — a page, a sample, a fixed-size exportROW_NUMBERIt never ties, so the row count is guaranteed
“Everyone who placed in the top n” — a leaderboard, a prize listRANKTied rows share a place, and the places reflect position
“The top n distinct levels” — the three highest salary bands, the five best scoresDENSE_RANKThe cutoff counts values, not rows

Which is why “give me the top four” is worth one clarifying question before we write the query.

Can all three take a partition?

They can, and nothing here is special in that respect. Add PARTITION BY and each function starts again in every group, exactly as in Episode 47.

Query
SELECT DEPT_ID, FIRST_NAME, SALARY,
       ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN,
       RANK()       OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RNK,
       DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DENSE
FROM EMPLOYEES
ORDER BY DEPT_ID NULLS LAST, RN;
Result8 rows · 6 columns · scroll →
DEPT_IDFIRST_NAMESALARYRNRNKDENSE
10Daniel131000.00111
10Aisha118000.00222
10Marcus96500.00333
20Lena104000.00111
20Tomas87250.00222
30Priya96500.00111
30Sofia71400.00222
NULLGrace92000.00111

This grid is worth stopping on, because all three columns are identical in it, which after everything we have just read looks like a mistake. It is not. There is no tie inside any department. Marcus and Priya are on the same salary, but Marcus is in department 10 and Priya is in 30. The partition separated them and left the three functions nothing to disagree about.

A thing to carry into production

The three functions differ only where values repeat within a partition. Partitioning a table finely enough can make the choice between them irrelevant — and coarsening it later can make the choice matter again, silently.

Let's try it on a bigger table

One more, on ORDERS, where ranking within a group is what we would actually want. Let's rank each customer's orders by amount, largest first. The query carries LIMIT 12, so the grid is the first stretch of the result rather than all of ORDERS.

Query
SELECT CUSTOMER_ID, ORDER_ID, AMOUNT,
       RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY AMOUNT DESC) AS RNK
FROM ORDERS
ORDER BY CUSTOMER_ID, RNK
LIMIT 12;
Result12 rows · 4 columns
CUSTOMER_IDORDER_IDAMOUNTRNK
10110409640.001
10110358820.002
10110118100.003
10110297460.004
10110236730.005
10110036150.006
10110175540.007
10110065020.008
10110014200.009
10210281240.001
10210081120.002
10210391080.003

Customer 101 has nine orders in this grid, ranked 1 through 9 with no repeats, which tells us there are no duplicate amounts among them. Then the numbering starts again at 1 for customer 102.

That shape, rank inside a group and then filter on the rank, is top-n-per-group, and it gets an episode of its own at the end of this level. The filter goes on the outside of a wrapper or in a QUALIFY clause, for the same reason as in Episode 49. You cannot test a window function in the WHERE of the query that computes it.

Key points

  • ROW_NUMBER never ties. RANK and DENSE_RANK both give equal values equal numbers — that is what makes them rankings rather than numberings.
  • The two differ only on the row after a tie. RANK skips; DENSE_RANK does not.
  • RANK counts rows: your rank is one plus the number of rows strictly ahead of you, so a tie of two leaves a hole of one in the sequence.
  • DENSE_RANK counts distinct values: your dense rank is one plus the number of distinct values ahead of yours, so there are never any holes.
  • MAX(DENSE_RANK) always equals the number of distinct values in the ordering column. MAX(RANK) equals the row count as long as the lowest value is not itself shared. Both functions produce the same quantity of distinct numbers.
  • A cutoff means three different things: exactly n rows (ROW_NUMBER), everybody who placed in the top n (RANK), or the top n distinct levels (DENSE_RANK).
  • RANK and DENSE_RANK can return the same row count for the same cutoff — they do on this table. They come apart when a tie sits above the cutoff rather than on it.
  • All three take PARTITION BY, and all three require an ORDER BY inside OVER.
  • If no value repeats inside a partition, the three functions return identical columns. The choice between them only ever matters where there is a tie.
Next in this series

In the next post we will look at NTILE, PERCENT_RANK and CUME_DIST. We move from which position am I in to where am I in the distribution — quartiles, percentiles and the cumulative share of rows at or below a value.

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 and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake RANK documentation and the DENSE_RANK documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)