Window Functions: Ranking and Partitions

GROUP BY answers questions about groups, and the price is that individual rows disappear. Ask for the average salary per department and you get one row per department. The employees are gone.

But some questions need both at once. Show every employee and where they rank in their department. Show each order and the customer's running total. A window function computes across a set of related rows and keeps every original row.

The anatomy#

function(...) OVER (
    PARTITION BY ...    -- which rows form the window (optional)
    ORDER BY ...        -- ordering within the window (optional)
    frame clause        -- which rows around the current one (optional, next lesson)
)

OVER is what makes a function a window function. PARTITION BY splits the rows into independent groups, like GROUP BY does, but without collapsing them. ORDER BY inside OVER orders rows within each partition for the calculation. It is separate from the query's own ORDER BY.

A first example#

Total salary of each department, shown next to every employee:

SELECT full_name, department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct
FROM employees
WHERE department_id = 2
ORDER BY salary DESC;

   full_name   | department_id | salary | dept_total | pct
---------------+---------------+--------+------------+------
 Priya Nair    |             2 | 130000 |     315000 | 41.3
 Lena Fischer  |             2 |  95000 |     315000 | 30.2
 Daniel Okafor |             2 |  90000 |     315000 | 28.6

Every employee keeps their own row, and each carries the department total beside them. A GROUP BY version would need a self-join to produce the same thing.

Ranking functions#

Three functions number rows within a partition according to the window's ORDER BY. They differ only in how they treat ties, and the course data has a tie on purpose: Sofia Martins and Kenji Watanabe both earn 120000.

SELECT full_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS rn,
       RANK()       OVER (ORDER BY salary DESC)              AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)              AS drnk
FROM employees
ORDER BY salary DESC, employee_id;

   full_name    | salary | rn | rnk | drnk
----------------+--------+----+-----+------
 Meera Iyer     | 185000 |  1 |   1 |    1
 Arjun Rao      | 140000 |  2 |   2 |    2
 Priya Nair     | 130000 |  3 |   3 |    3
 Sofia Martins  | 120000 |  4 |   4 |    4
 Kenji Watanabe | 120000 |  5 |   4 |    4
 Rahul Verma    | 110000 |  6 |   6 |    5
 Omar Haddad    | 100000 |  7 |   7 |    6
 Lena Fischer   |  95000 |  8 |   8 |    7
 Daniel Okafor  |  90000 |  9 |   9 |    8
 Grace Thompson |  70000 | 10 |  10 |    9

Look at the tie and what follows it.

• ROW_NUMBER never repeats. It hands out 1 to 10 regardless, so tied rows get different numbers.
• RANK gives ties the same number and then skips. Two people share rank 4, so the next rank is 6. Olympic medals work this way.
• DENSE_RANK gives ties the same number and does not skip. After 4 comes 5.

Which one is right depends on the question. "Top 3 earners" with ties usually wants DENSE_RANK or RANK. "Pick exactly one row per group" wants ROW_NUMBER.

Notice the employee_id tiebreaker in the ROW_NUMBER window. Without it, which of the two 120000 employees gets 4 and which gets 5 is not guaranteed and can change between runs. Add a tiebreaker whenever you need a deterministic answer.

Restarting per group#

Add PARTITION BY and the numbering restarts in each group.

SELECT p.category, p.name, p.unit_price,
       DENSE_RANK() OVER (PARTITION BY p.category ORDER BY p.unit_price DESC) AS price_rank
FROM products p
WHERE p.category IN ('Accessories', 'Furniture')
ORDER BY p.category, price_rank;

  category   |        name         | unit_price | price_rank
-------------+---------------------+------------+------------
 Accessories | Mechanical Keyboard |      89.00 |          1
 Accessories | USB-C Hub           |      45.00 |          2
 Accessories | Laptop Stand        |      34.00 |          3
 Accessories | Wireless Mouse      |      29.50 |          4
 Furniture   | Standing Desk       |     499.00 |          1
 Furniture   | Ergonomic Chair     |     349.00 |          2

NTILE: equal-sized buckets#

NTILE(n) splits the ordered rows into n buckets as evenly as possible. It is how you build quartiles, deciles and "top third" segments.

SELECT name, unit_price,
       NTILE(3) OVER (ORDER BY unit_price DESC) AS bucket
FROM products;

Eleven products into three buckets gives sizes 4, 4 and 3. The Standing Desk, Ergonomic Chair, Monitor and Headphones land in bucket 1, and the Cable Organizer, Desk Lamp and Wireless Mouse land in bucket 3. When the rows do not divide evenly, the first buckets get the extra rows.

Where window functions run#

Window functions are evaluated after WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. That has one big consequence: you cannot filter on a window result in the same query level.

SELECT full_name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;
-- ERROR:  window functions are not allowed in WHERE
-- (and "rn" does not exist yet at WHERE time anyway)

Wrap it, then filter the outer query.

SELECT * FROM (
    SELECT full_name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

Some databases (Snowflake, BigQuery, Teradata) offer QUALIFY to do this in one level. PostgreSQL does not, so the subquery or CTE is the idiom.

Pattern: top N per group#

The most requested use of ranking is "the best N in each category". The two highest-paid people in every department:

SELECT dept, full_name, salary
FROM (
    SELECT d.name AS dept, e.full_name, e.salary,
           ROW_NUMBER() OVER (
               PARTITION BY e.department_id
               ORDER BY e.salary DESC, e.employee_id
           ) AS rn
    FROM employees e
    JOIN departments d ON d.department_id = e.department_id
) t
WHERE rn <= 2
ORDER BY dept, rn;

    dept     |   full_name    | salary
-------------+----------------+--------
 Engineering | Meera Iyer     | 185000
 Engineering | Arjun Rao      | 140000
 Finance     | Rahul Verma    | 110000
 Sales       | Priya Nair     | 130000
 Sales       | Lena Fischer   |  95000
 Support     | Omar Haddad    | 100000
 Support     | Grace Thompson |  70000

Finance has only one employee, so it shows one row. Legal has none and does not appear at all, because an inner join dropped it.

Without window functions this takes a correlated subquery or a lateral join, and both are harder to read.

Pattern: keep the latest row per entity#

Same technique, with rn = 1. The latest order for each customer:

SELECT customer_id, order_id, order_date
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC, order_id DESC) AS rn
    FROM orders o
) t
WHERE rn = 1
ORDER BY customer_id;

 customer_id | order_id | order_date
-------------+----------+------------
           1 |       10 | 2024-05-02
           2 |        7 | 2024-04-05
           3 |       13 | 2024-06-03
           4 |        5 | 2024-03-02
           5 |        6 | 2024-03-18
           6 |       12 | 2024-05-21
           7 |        9 | 2024-04-25
           8 |       11 | 2024-05-10
           9 |       14 | 2024-06-14

This is the portable replacement for PostgreSQL's DISTINCT ON. The same pattern deduplicates data: partition by the columns that define "the same record", order by recency, keep rn = 1, delete the rest.

Under the hood#

To compute a window the database sorts the input by the partition columns, then by the window's ORDER BY, then streams through, maintaining running state. In the plan you will see a WindowAgg node on top of a Sort.

An index that already delivers rows in that order lets the planner skip the sort. Several window functions sharing the same OVER clause are computed in a single pass. Different OVER clauses mean more sorts, so reuse window definitions where you can.

Common mistakes#

Filtering on a window function in WHERE. It does not exist yet at that point. Use a subquery or CTE.

Using ROW_NUMBER with ties and no tiebreaker. The answer is nondeterministic and a rerun can pick a different row.

Picking RANK when you wanted DENSE_RANK. "Top 3" with a tie at second returns a different number of rows.

Confusing the ORDER BY inside OVER with the query's ORDER BY. They are independent. The first shapes the calculation, the second sorts the output.

Using GROUP BY plus a self-join when a window function would do. It is slower and longer.

Practice#

1. Rank all products by price within their category using RANK. Do any ties appear in this data? Change one price to create a tie and watch RANK and DENSE_RANK diverge, then roll back.
2. Return the cheapest product in each category using ROW_NUMBER. Cable Organizer should win Office.
3. For each employee, show their salary as a percentage of their department total, rounded to 1 decimal. Sofia Martins and Kenji Watanabe should both show 21.2.
4. Find every customer's first order (earliest order_date) along with the number of days since the customer's created_at date.
5. Deduplication drill: run CREATE TABLE customers_copy AS SELECT * FROM customers; (it copies no constraints), insert a duplicate email on purpose, then write the ROW_NUMBER query that selects the duplicates to remove.