Skip to main content

​
Window functions

Window functions perform calculations across a set of rows related to the current row.

​
ROW_NUMBER

Assigns a unique sequential number to each row within a partition. 
SELECT
  user_id,
  event_timestamp,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS event_seq
FROM company_data."123"

​
RANK

Assigns a rank with gaps for ties. 
SELECT
  user_id,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM company_data."123"

​
DENSE_RANK

Assigns a rank without gaps for ties. 
SELECT
  user_id,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM company_data."123"

​
PERCENT_RANK

Returns the relative rank as a percentage (0 to 1). 
SELECT
  user_id,
  score,
  PERCENT_RANK() OVER (ORDER BY score) AS percentile
FROM company_data."123"

​
LAG

Returns the value from a previous row. 
SELECT
  event_date,
  value,
  LAG(value, 1) OVER (ORDER BY event_date) AS previous_value,
  value - LAG(value, 1) OVER (ORDER BY event_date) AS change
FROM company_data."123"

​
LEAD

Returns the value from a following row. 
SELECT
  event_date,
  value,
  LEAD(value, 1) OVER (ORDER BY event_date) AS next_value
FROM company_data."123"

​
SUM / AVG / COUNT (windowed)

Aggregate functions can be used as window functions. 
SELECT
  event_date,
  amount,
  SUM(amount) OVER (ORDER BY event_date) AS running_total,
  AVG(amount) OVER (PARTITION BY category) AS category_avg
FROM company_data."123"

​
Related content

Aggregate Functions

GROUP BY computations

SELECT

QUALIFY clause for window function filtering

All Functions

Browse all function categories

Common Query Patterns

Real-world deduplication examples