Partitioning data in a window function

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Ranking data

Here, we're ranking all records in the result set!

      user_id  |    event_name   |  km_traveled  |  closest_attendees 
      -------  | --------------- | ------------- | ----------------- 
      user_81  |  Lunar Drift    |      0.5      |         1         
      user_02  |  Lunar Drift    |      1.1      |         2       
      user_33  |  Crimson Arc    |      8.6      |         3          
      user_33  |  Neon Prophet   |      8.6      |         3         
      user_15  |  VibeStorm      |      17       |         5          
      user_94  |  The Dusk Owls  |      41       |         6        
      user_47  |  Lunar Drift    |      61       |         7   
      user_56  |  Crimson Arc    |      116      |         8

Ranking data with partitions

Now, we want to rank data for each specified window.

A table that ranks concert attendees based on the distance they traveled to each venue

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Create window by event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY helps us create windows of records to apply functions to

PARTITION BY goes before ORDER BY in OVER(...)
Similar to GROUP BY, but does not "collapse" records

Ranking data with partitions

SELECT
    level,
    price,

    RANK() OVER(
        PARTITION BY level
        ORDER BY price DESC
    ) AS price_rank

FROM CONCERTS.attendance;

PARTITION BY creates windows

      level  |   price   | price_rank 
    -------- | --------- | -----------
       100   |    765    |      1
       100   |    617    |      2
       100   |    490    |      3
       100   |    490    |      3

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Generating summary metrics with FIRST_VALUE

    FIRST_VALUE(<1>) OVER(
        PARTITION BY <2>
        ORDER BY <3>
    ) AS <alias>

FIRST_VALUE will help to find the first value in a window

<1>: which column in record to return

<2>: field to partition data by

<3>: field to determine first record

Generating summary metrics with AVG

AVG(<1>) OVER(
    PARTITION BY <2>
    -- No need to ORDER BY!
) AS <alias>

AVG will find the mean value of a field for each window

<1>: column to take the average of

<2>: field to partition data by

... no need for ORDER BY!

Customer satisfaction

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Satisfaction score for the closest concert-goer
        ORDER BY km_traveled
    ) AS first_score,


    -- Find the average satisfcation score for a "window" of records
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

Customer satisfaction


      user_id  |   event_name   |  satisfaction_score  |  first_score  |  average_score 
     --------- | -------------- | -------------------- | ------------- | ---------------

      user_26  |  Pulse Theory  |          71          |       98      |      84.5      
      user_92  |  Pulse Theory  |          98          |       98      |      84.5      

                                          ...

      user_57  |   Nova Sway    |           4          |       22      |      29.3      
      user_39  |   Nova Sway    |          22          |       22      |      29.3      
      user_44  |   Nova Sway    |          62          |       22      |      29.3      

                                          ...

Let's practice!

Window Functions in Snowflake