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 in Snowflake

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?
Window Functions in Snowflake

Traditional functions

Traditional aggregation function must have a GROUP BY clause.

A visual representation of an aggregation function.

Window Functions in Snowflake

Window functions

A visual representation of the output of a window function.

                                                 Do not require a GROUP BY!

Window Functions in Snowflake

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!
Window Functions in Snowflake

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 ...)!

Window Functions in Snowflake

Let's practice!

Window Functions in Snowflake

Preparing Video For Download...