SQL Window Functions: ROW_NUMBER, RANK, DENSE_RANK, and Running Totals Explained

Window functions are one of the most powerful features in SQL — and one of the most feared. They let you compute values across a set of rows related to the current row without collapsing the result into fewer rows the way GROUP BY does.

In this guide you'll learn exactly how ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running totals work — with a consistent employee dataset so you can see exactly what each one returns.

The sample dataset

CREATE TABLE employees (
  id         INT,
  name       VARCHAR(50),
  department VARCHAR(50),
  salary     INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   'Engineering', 90000),
  (2, 'Bob',     'Engineering', 85000),
  (3, 'Carol',   'Engineering', 85000),
  (4, 'Raman',   'Data',        88000),
  (5, 'Eve',     'Data',        76000),
  (6, 'Frank',   'Marketing',   70000);

Window function syntax

Every window function follows the same pattern:

FUNCTION_NAME() OVER (
  [PARTITION BY column]   -- optional: restart numbering per group
  [ORDER BY    column]    -- defines the order within the window
  [ROWS/RANGE  frame]    -- optional: frame boundaries
)

ROW_NUMBER — unique sequential rank

ROW_NUMBER() assigns a unique integer to each row, starting from 1. When two rows have the same salary, they still get different numbers — the tie-breaking is arbitrary.

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

Bob and Carol both earn 85,000 but get different row numbers (3 and 4). The order between tied rows is not guaranteed.

RANK — gaps on ties

RANK() gives tied rows the same number, then skips the next number(s).

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM   employees;

Bob and Carol are both rank 3. Eve jumps to rank 5 — rank 4 is skipped. Think of it like sports rankings: if two runners tie for gold, the next is bronze (third), not silver.

DENSE_RANK — no gaps on ties

DENSE_RANK() is like RANK but never skips numbers.

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM   employees;

Eve is dense_rank 4, not 5. Use DENSE_RANK when "find the Nth highest" is the goal — it's what most interview questions expect.

The classic interview question

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM   employees
) ranked
WHERE  dr = 2;

PARTITION BY — restart per group

Add PARTITION BY to run the window function independently per group. This is the equivalent of GROUP BY within a window function.

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

Running totals with SUM OVER

SELECT id, name, salary,
       SUM(salary) OVER (
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM   employees;

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the first row up to and including the current row." That's the default when you specify ORDER BY without a frame, so this is equivalent:

SELECT id, name, salary,
       SUM(salary) OVER (ORDER BY id) AS running_total
FROM   employees;

LAG and LEAD — access adjacent rows

LAG(col, n) looks n rows back; LEAD(col, n) looks n rows ahead.

SELECT name, salary,
       LAG(salary, 1) OVER (ORDER BY id) AS prev_salary,
       salary - LAG(salary, 1) OVER (ORDER BY id) AS change
FROM   employees;

Quick reference

Practice problems

The best way to solidify window functions is to write them. Head to the SQL Practice section — Q5 (ranking), Q7 (running total), and Q10 (month-over-month) are all window function problems with real sample data you can run in the browser.