Window functions: ROW_NUMBER, RANK, SUM OVER

Compute ranks, running totals, and within-group comparisons without collapsing rows thanks to OVER and PARTITION BY.

10 min · 3 questions

GROUP BY collapses rows into groups; a window function computes an aggregate or a rank while keeping every row. It is the tool for top N per group, running totals, and comparisons to your own team's average. Available in SQLite since version 3.25 (2018). Load the dataset.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

Three ranking functions, three behaviors when facing ties: ROW_NUMBER numbers without duplicates (arbitrary tie-break), RANK gives ties the same rank then skips (1, 2, 2, 4), DENSE_RANK does not skip (1, 2, 2, 3). The WINDOW clause avoids repeating the window definition (supported by SQLite and PostgreSQL, not by SQL Server).

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

PARTITION BY restarts the computation for each subset: one window per department, collapsing nothing. Combined with a CTE, this gives the interview pattern: the N highest paid in each department. The CTE is mandatory because a window function is evaluated after the WHERE: you cannot filter on rn directly.

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

Classic aggregates become windows with OVER: SUM(salary) OVER (ORDER BY ...) produces a running total row by row. Specify the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a strictly row-by-row running total.

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

Knowledge check

Make sure you remember the key points of this lesson.

1. With salaries 5200, 4800, 4800, 4100, which rank does RANK() assign to 4100?
   • 3
   • 4
   • 2
2. Why do you need a CTE to filter on ROW_NUMBER()?
   • OVER is evaluated after WHERE, so it is forbidden inside it
   • A CTE is always more efficient
   • ROW_NUMBER only works inside a WITH
3. What does PARTITION BY dept do in an OVER clause?
   • It sorts the final result by department
   • It restarts the window computation for each department
   • It merges rows into a single one per department