Congratulations!

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Window functions

Calculations are performed on "windows" of records without losing row-level detail

A visual representation of the output of a window function.

Ranking window functions

SELECT
    workout_duration,

    RANK() OVER(
        ORDER BY workout_duration DESC
    ) AS r,

    -- Without gaps!
    DENSE_RANK() OVER(
        ORDER BY workout_duration DESC
    ) AS dr

FROM FITNESS.workouts;

   workout_duration  |   r   |   dr
  ------------------ | ----- | -----
         78          |   1   |   1
         71          |   2   |   2
         71          |   2   |   2

         68*         |   4*  |   3*

         67          |   5   |   4
         67          |   5   |   4
         67          |   5   |   4
         63          |   8   |   5
         61          |   9   |   6

Aggregation window functions

      m_id  |       wd     |   cb  |  num_workouts  |  total_cb  |  cb_vs_average 
     ------ | ------------ | ----- | -------------- | ---------- | ---------------
     m_192  |  2024-01-01  |  105  |        4       |     432    |      -3
     m_192  |  2024-01-03  |  156  |        4       |     432    |      48
     m_192  |  2024-01-04  |   69  |        4       |     432    |     -39
     m_192  |  2024-01-10  |  102  |        4       |     432    |      -6

     m_74   |  2024-02-10  |  374  |        3       |    1274    |     -50.67   
     m_74   |  2024-02-13  |  396  |        3       |    1274    |     -28.67
     m_74   |  2024-02-14  |  504  |        3       |    1274    |      79.33

     m_233  |  2024-03-05  |  51   |        2       |     132    |     -15
     m_233  |  2024-03-12  |  81   |        2       |     132    |      15

Congratulations!

Window functions

Ranking window functions

Aggregation window functions

Running and moving calculations

What's next?

Thank you!