Window Functions

GROUP BY answers "what's the average salary per department," but it collapses every department down to one summary row, you lose the individual employees. A window function answers a different kind of question, "what's this employee's salary rank within their department," while keeping every individual row visible.

Learning Objectives

• Explain how a window function differs from GROUP BY.
• Rank rows with ROW_NUMBER, RANK, and DENSE_RANK, and explain how they handle ties differently.
• Use PARTITION BY to restart a calculation within each group, without collapsing rows.

The Basic Shape: OVER()

Every window function is followed by OVER(...), which defines the "window" of rows it operates across. With no PARTITION BY, the window is the entire result set, every employee is ranked against everyone else. Critically, all three original rows are still here, nothing got collapsed.

SELECT first_name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS pay_rank
FROM employees;

-- Result:
-- first_name | department  | salary | pay_rank
-- Grace      | Engineering | 98000  | 1
-- Ada        | Engineering | 95000  | 2
-- Margaret   | Research    | 87000  | 3

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT first_name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- With two employees tied at 95000:
-- salary | row_num | rank_num | dense_rank_num
-- 98000  | 1       | 1        | 1
-- 95000  | 2       | 2        | 2
-- 95000  | 3       | 2        | 2
-- 87000  | 4       | 4        | 3

PARTITION BY: Restarting Per Group

PARTITION BY department splits the rows into separate windows, one per department, and the ranking restarts from 1 within each. This is the window-function equivalent of GROUP BY department, except every individual employee row is still here.

SELECT first_name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Result:
-- first_name | department  | salary | dept_rank
-- Grace      | Engineering | 98000  | 1
-- Ada        | Engineering | 95000  | 2
-- Margaret   | Research    | 87000  | 1

Running Totals

Aggregate functions work as window functions too. Here, SUM() OVER (ORDER BY ...) computes a running total, each row's cumulative sum through that point in the ordering, rather than one grand total for the whole table.

SELECT first_name, salary,
  SUM(salary) OVER (ORDER BY salary DESC) AS running_total
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Reach for a window function whenever you need a per-row calculated value that still depends on other rows, ranking, running totals, comparisons to a previous row, without collapsing the result.
• Choose RANK, DENSE_RANK, or ROW_NUMBER deliberately based on how ties should actually behave, don't default to one without thinking about it.
• Filter a window function's output using a subquery or CTE, never directly in the same query's WHERE.
• Check your database version before relying on window functions, especially on an older MySQL (pre-8.0) or SQLite (pre-3.25) instance.

Interview Questions

Summary

This lesson covered the OVER() clause, ranking rows with ROW_NUMBER, RANK, and DENSE_RANK and how each handles ties differently, splitting a calculation per group with PARTITION BY without collapsing rows, and running totals with an aggregate function used as a window function.

What's Next?

The next lesson covers Common Table Expressions (CTEs), naming a subquery with WITH for readability and reuse, including recursive CTEs.