What are Window Functions?

What You'll Learn

What window functions are
ROW_NUMBER, RANK, DENSE_RANK
Aggregate window functions

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

Basic Syntax

query.sqlSQL

function() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN ... AND ...
)

ROW_NUMBER, RANK, DENSE_RANK

query.sqlSQL

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- With same salaries (100, 100, 90):
-- ROW_NUMBER: 1, 2, 3
-- RANK:       1, 1, 3  (skips 2)
-- DENSE_RANK: 1, 1, 2  (no skip)

PARTITION BY

query.sqlSQL

-- Rank within each department
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

Aggregate Window Functions

query.sqlSQL

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg,
  SUM(salary) OVER (PARTITION BY department) as dept_total,
  salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

Running Totals

query.sqlSQL

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7day
FROM sales;

LEAD and LAG

query.sqlSQL

SELECT
  date,
  price,
  LAG(price, 1) OVER (ORDER BY date) as prev_price,
  LEAD(price, 1) OVER (ORDER BY date) as next_price,
  price - LAG(price, 1) OVER (ORDER BY date) as change
FROM stock_prices;