Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row like aggregate functions do. Each row retains its identity in the result.

Syntax

function_name(args) OVER (
  [PARTITION BY col1, col2, ...]
  [ORDER BY col3 [ASC|DESC], ...]
  [frame_spec]
)

• PARTITION BY divides rows into groups (partitions). The function is computed independently for each partition.
• ORDER BY defines the row ordering within each partition.
• frame_spec defines which rows around the current row are included in the computation.

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential integer to each row within its partition.

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rank_in_dept
FROM employees;

Result:

RANK()

Like ROW_NUMBER() but assigns the same rank to ties and leaves gaps.

SELECT
  name, score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM contestants;

Result (with ties):

DENSE_RANK()

Like RANK() but without gaps in the ranking sequence.

SELECT
  name, score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM contestants;

Result:

NTILE(n)

Distributes rows into n roughly equal-sized buckets.

-- Divide employees into 4 salary quartiles
SELECT
  name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Windowed Aggregates

Standard aggregate functions can be used as window functions by adding an OVER clause.

Running Totals

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Result:

Partition Aggregates

-- Each row shows the department average alongside individual salary
SELECT
  name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

All Windowed Aggregates

SELECT
  name, department, salary,
  SUM(salary)   OVER (PARTITION BY department) AS dept_total,
  AVG(salary)   OVER (PARTITION BY department) AS dept_avg,
  MIN(salary)   OVER (PARTITION BY department) AS dept_min,
  MAX(salary)   OVER (PARTITION BY department) AS dept_max,
  COUNT(*)      OVER (PARTITION BY department) AS dept_count
FROM employees;

Frame Specifications

Frame specs define which rows relative to the current row are included in the window computation.

ROWS BETWEEN start AND end

Where start and end can be:

Moving Average

-- 3-day moving average
SELECT
  trade_date,
  close_price,
  AVG(close_price) OVER (
    ORDER BY trade_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM stock_prices;

Running Total (Explicit Frame)

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

Centered Window

-- Smoothing with a 5-row centered window
SELECT
  ts,
  value,
  AVG(value) OVER (
    ORDER BY ts
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  ) AS smoothed
FROM sensor_readings;

Combining Multiple Windows

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  RANK()       OVER (ORDER BY salary DESC) AS overall_rank,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg
FROM employees
ORDER BY department, salary DESC;