Assigning row numbers

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Window functions

Used to perform a calculation across a "window" of rows and return a value for each row

Ranking

RANK, DENSE_RANK
FIRST_VALUE, LAST_VALUE, NTH_VALUE
LAG, LEAD

Aggregation

COUNT, SUM, AVG

Window functions

Window functions help us answer new types of questions!

What is the third most popular concert at each venue?
How did sales for an item vary day-to-day?
Is my rolling average of viewership trending up or down?

Traditional functions

Traditional aggregation function must have a GROUP BY clause.

A visual representation of an aggregation function.

Window functions

A visual representation of the output of a window function.

Do not require a GROUP BY!

Assigning row numbers

SELECT
    <other-fields>,

    ROW_NUMBER() OVER(
        ORDER BY <field>
    ) AS <alias>

FROM SCHEMA.table

...;

The ROW_NUMBER() function assigns a row number to each record in a result set

OVER() defines the window
Alias the resulting column
No GROUP BY!

Concert attendance

SELECT
    customer_id,
    event_name,

    -- Assign a row number to each 
    -- record in the result set

    ROW_NUMBER() OVER(
        ORDER BY time_spent_minutes
    ) AS row_num

FROM concerts.attendance;

  customer_id  |  event_name  |  row_num  
 ------------- | ------------ | ---------
     1931292   |  Music Fest  |    1    
     5462384   |  Elton John  |    2    
     7732891   |  Coachella   |    3    
     3124888   |  Porch Fest  |    4

Remember the syntax: ROW_NUMBER() OVER( ORDER BY ...)!

Let's practice!

Window Functions in Snowflake