Window Functions in Snowflake
Jake Roach
Field Data Engineer
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 help us answer new types of questions!
$$
Traditional aggregation function must have a GROUP BY
clause.
Do not require a GROUP BY
!
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 windowGROUP BY
!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