Skip to content

Window Functions

Window functions perform calculations across a set of rows that are related to the current row, without collapsing the result set like aggregate functions do. They are essential for analytics tasks such as running totals, moving averages, rankings, and row comparisons.

Function Categories

Cumulative Functions

Cumulative functions calculate values from the first row up to the current row within a partition.

Moving Window Functions

Moving window functions calculate values within a sliding window of rows relative to the current row.

Row Position Functions

Row position functions access values from specific rows relative to the current row or within the partition.

  • Lag - Returns value from previous row
  • Lead - Returns value from following row
  • First - Returns first value in partition
  • Last - Returns last value in partition

Ranking Functions

Ranking functions assign ranks or positions to rows within a partition based on ordering.

  • Rank - Assigns rank with gaps
  • DenseRank - Assigns rank without gaps
  • PercentRank - Calculates relative rank as percentage
  • Ntile - Divides rows into groups
  • RowNumber - Assigns sequential row numbers

Running Total Functions

Running total functions calculate cumulative values from the start of the partition.

Common Use Cases

Time Series Analysis

Window functions are ideal for analyzing data over time:

CumulativeSum([Sales])

Track year-to-date or month-to-date sales totals.

MovingAvg([Revenue], -6, 0)

Calculate a 7-day moving average to smooth out daily fluctuations.

Comparative Analysis

Compare current values with historical data:

Lag([Price], 7)

Compare current price to the price from 7 days ago.

[Sales] - Lag([Sales], 1)

Calculate day-over-day change in sales.

Ranking and Segmentation

Identify top performers or segment data:

Rank()

Rank products by sales within each category.

Ntile(4)

Divide customers into quartiles based on purchase amount.

Statistical Analysis

Track variability and correlations over time:

MovingStdDev([Response_Time], -29, 0)

Monitor 30-day rolling volatility in response times.

CumulativeCorr([Marketing_Spend], [Sales])

Track how the correlation between marketing and sales evolves.