Window Functions

Advanced

⏱️ 25 mins read

Ranks employees (Topic 1) and walks orders (Topic 7) — windows add answers to rows without collapsing them.

What You'll Learn

Window functions perform calculations across rows related to the current row WITHOUT collapsing them like GROUP BY. They use the OVER() clause with optional PARTITION BY (grouping) and ORDER BY (ordering within partition). Key functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG, FIRST_VALUE, LAST_VALUE.

Syntax

FUNCTION() OVER (
  PARTITION BY col
  ORDER BY col
  ROWS BETWEEN ... AND ...
)

Example

-- ROW_NUMBER, RANK, DENSE_RANK per department
SELECT name, salary, department_id,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drnk
FROM employees;

-- LAG: compare each order to the previous one, by date
SELECT
  order_date,
  total,
  LAG(total) OVER (ORDER BY order_date)         AS prev_total,
  total - LAG(total) OVER (ORDER BY order_date) AS change_from_prev
FROM orders;

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

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary
-- 1  | Ada     | 1             | 145000
-- 2  | Grace   | 1             | 115000
-- 3  | Alan    | 2             | 92000
-- 4  | Edsger  | NULL          | 78000
-- 5  | Barbara | 3             | 64000

-- orders (from Topic 7)
-- id | total  | order_date
-- 1  | 240.00 | 2024-01-15
-- 2  | 89.90  | 2024-02-03
-- 3  | 430.00 | 2024-03-11
-- 4  | 59.50  | 2024-04-02
-- 5  | 120.00 | 2024-05-20

Windows vs GROUP BY: rows stay, answers get added

GROUP BY department_id gives you 4 rows (Topic 9). The same PARTITION BY gives every employee their group's stats while keeping all 5 rows — Edsger included, in his own NULL partition, exactly like GROUP BY's NULL group. GROUP BY answers 'per group'; windows answer 'per group, per row'.

SELECT name, salary,
  COUNT(*)    OVER (PARTITION BY department_id) AS dept_headcount,
  MAX(salary) OVER (PARTITION BY department_id) AS dept_top_salary
FROM employees;
-- Ada     | 145000 | 2 | 145000
-- Grace   | 115000 | 2 | 145000
-- Alan    | 92000  | 1 | 92000
-- Edsger  | 78000  | 1 | 78000   <- his own NULL partition
-- Barbara | 64000  | 1 | 64000
If you ever write a GROUP BY and then re-join it back to the detail rows to 'add back' an aggregate — that whole dance is one window function.

Running totals and LAG without self-joins

Before windows, a running total meant a self-join or a correlated subquery (Topic 16's per-row cost). The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW does it in one ordered pass. Note the first order's LAG is NULL — date comparisons on that row must expect it (Topic 7 again).

SELECT order_date, total,
  SUM(total) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;
-- 240.00 → 329.90 → 759.90 → 819.40 → 939.40

SELECT order_date,
  total - LAG(total) OVER (ORDER BY order_date) AS delta
FROM orders;
-- First row: LAG is NULL → delta is NULL, not 0
A window's default frame ends at the current row of the ordering — spell out the frame whenever the boundary matters, and NULL-check the first row after LAG.

Windows can't live in WHERE — wrap them

WHERE runs before SELECT (Topic 2), so window results don't exist yet when it filters. The fix is always the same: compute the window in a CTE (Topic 17), filter outside. This is exactly how Topic 16's slow correlated 'top earner per department' becomes one clean pass.

WITH ranked AS (
  SELECT name, salary, department_id,
    ROW_NUMBER() OVER (
      PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, salary, department_id
FROM ranked WHERE rn = 1;
-- Ada (1), Alan (2), Barbara (3) — and no NULL-group surprise:
-- Edsger is rank 1 of his own partition, so he appears too
Filter-on-a-window = CTE + WHERE outside. It's not a workaround; it IS the pattern — and notice it answered Topic 16's question without a single per-row subquery.

Common Mistakes

Confusing RANK and DENSE_RANK: RANK gives 1,2,2,4 (skips 3). DENSE_RANK gives 1,2,2,3 (no skip). ROW_NUMBER always gives unique sequential numbers regardless of ties. Window functions cannot be used in WHERE — wrap in a CTE or subquery.

Interview Tips

Window functions are the #1 topic at FAANG-level SQL interviews. Know ROW_NUMBER for deduplication (keep latest row), RANK/DENSE_RANK for top-N per group, LAG/LEAD for period-over-period comparisons, and running totals.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. The core difference between window functions and GROUP BY?

2. COUNT(*) OVER (PARTITION BY department_id) on ShopCo shows Ada…?

3. Which partition does Edsger (department_id NULL) land in?

4. The running-total window needs which frame?

5. LAG(total) on the FIRST order (by order_date) returns…?

6. Why can't you write WHERE rn = 1 directly on ROW_NUMBER()?

Practice

Return each employee's name, department_id, and salary, plus their salary rank within their department (1 = highest).

⚡ Solve it in the SQL playground →

Frequently Asked Questions

When do I still need GROUP BY instead of a window?

When the output should be one row per group — counts, totals, averages for a report. Windows keep detail rows; GROUP BY collapses them. If you catch yourself GROUP BY-ing and then re-joining detail rows, you wanted a window.

Do window functions hurt performance?

They need their input sorted by PARTITION BY + ORDER BY, which is real cost on large tables — but it is one sort versus a GROUP BY plus a self-join, which is usually worse. An index matching the window's ordering can eliminate the sort (Topic 21). EXPLAIN and compare (Topic 22).

Back to Full Roadmap