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"

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

Array & Map Functions Geospatial Functions

⌘I

Overview

Data Types

SQL Commands

Operators

Functions

General Reference

Window Functions