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 GROUP BY. This lesson covers ranking, offset, aggregate window functions, PARTITION BY, and frame clauses.

---

What Are Window Functions?

A window function operates on a "window" (a set of rows) defined relative to the current row:

SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER () AS company_avg,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Unlike GROUP BY, every original row is preserved in the output.

---

Ranking Functions

ROW_NUMBER

Assigns a unique sequential number within each partition:

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

RANK and DENSE_RANK

SELECT
  name,
  score,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results;

Score	RANK	DENSE_RANK
95	1	1
95	1	1
88	3	2
80	4	3

• RANK leaves gaps after ties (1, 1, 3, 4).
• DENSE_RANK does not leave gaps (1, 1, 2, 3).

Top-N Per Group

-- Top 3 highest-paid employees per department
SELECT * FROM (
  SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

---

Offset Functions: LAG and LEAD

Access values from previous or next rows without a self-join:

LAG

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

LEAD

-- Days until next order for each customer
SELECT
  customer_id,
  order_date,
  LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order,
  LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) - order_date AS days_between
FROM orders;

LAG/LEAD with Defaults

-- Provide a default when there is no previous row
SELECT
  order_date,
  total,
  LAG(total, 1, 0.00) OVER (ORDER BY order_date) AS prev_total
FROM orders;

---

Aggregate Window Functions

Any aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function with OVER:

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total,
  AVG(total) OVER (ORDER BY order_date) AS running_avg,
  COUNT(*)   OVER (ORDER BY order_date) AS running_count
FROM orders;

Running Total Per Group

SELECT
  department,
  employee_id,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY employee_id
  ) AS dept_running_total
FROM employees;

---

PARTITION BY

PARTITION BY divides the rows into groups (partitions). The window function is applied independently within each partition: