Window Functions: ROW_NUMBER, RANK, and PARTITION BY

Window functions perform a calculation across a set of rows related to the current row — without collapsing those rows into a single output row the way GROUP BY does. This lets you compute rankings, running totals, and per-group comparisons while still returning every original row, which is something a plain aggregate query cannot do.

The syntax always follows the same shape: a function call followed by an OVER (...) clause that defines the "window" — optionally partitioned into groups and ordered within each group.

Why it matters

"The top three products in each category", "each sale as a percentage of its month's total" and "a running balance" all need a calculation across related rows without collapsing them. Window functions do exactly that. They replace clumsy self-joins and are a favourite of interviewers for data and back-end roles.

ROW_NUMBER, RANK, and DENSE_RANK

ROW_NUMBER() assigns a strictly increasing, unique number to each row within its window, even if values tie. RANK() assigns the same rank to tied rows but then skips the next rank number(s) (1, 2, 2, 4). DENSE_RANK() also gives tied rows the same rank but never skips a number (1, 2, 2, 3). Choosing between them depends on whether gaps after ties are meaningful for your use case.

PARTITION BY: Resetting the Window per Group

PARTITION BY column inside the OVER clause restarts the window function's calculation independently for each distinct value of that column — for example, ranking products within each category separately rather than across the whole table. Without PARTITION BY, the window spans the entire result set. You combine it with ORDER BY inside the same OVER clause to control the ranking order within each partition.

A Concrete Ranking Example

A very common real-world pattern is "top N per group" — for example, the two highest-priced products in each category. Window functions make this a single clean query: rank products within each category by price, then filter on that rank in an outer query (since window functions cannot be used directly inside a WHERE clause).

Examples

SELECT
    name,
    category_id,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM products;

      name       | category_id | price  | price_rank
------------------+-------------+--------+------------
 UltraBook 14     |           1 | 999.00 |          1
 Budget Laptop    |           1 | 399.00 |          2
 Wireless Mouse   |           2 |  29.99 |          1

WITH ranked AS (
    SELECT name, category_id, price,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
    FROM products
)
SELECT name, category_id, price FROM ranked WHERE rn <= 2;

      name       | category_id | price
------------------+-------------+--------
 UltraBook 14     |           1 | 999.00
 Budget Laptop    |           1 | 399.00
 Wireless Mouse   |           2 |  29.99

Exercise

Return the most expensive product in each category: the category, the product name and its price. Use ROW_NUMBER() partitioned by category, and order the final result by category.

Expected output: category | name | price, books | Atlas | 900, pens | Gold pen | 300 (one per line)

CREATE TABLE products (id INTEGER PRIMARY KEY, category TEXT, name TEXT, price INTEGER);
INSERT INTO products (id, category, name, price) VALUES
  (1, 'pens', 'Ball pen', 12),
  (2, 'pens', 'Gold pen', 300),
  (3, 'books', 'Atlas', 900),
  (4, 'books', 'Notebook', 60);

-- TODO: the most expensive product of each category

CREATE TABLE products (id INTEGER PRIMARY KEY, category TEXT, name TEXT, price INTEGER);
INSERT INTO products (id, category, name, price) VALUES
  (1, 'pens', 'Ball pen', 12),
  (2, 'pens', 'Gold pen', 300),
  (3, 'books', 'Atlas', 900),
  (4, 'books', 'Notebook', 60);

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS position
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE position = 1
ORDER BY category;

Quick quiz

Interview questions