Ranking with Window Functions

Imagine you need to show leaderboard positions, product bestsellers, or employee performance ranks in your PostgreSQL database. Doing it with a simple ORDER BY gives you a sorted list, but it doesn't assign numbers, and using subqueries with COUNT() quickly becomes slow and unreadable. Window functions—especially the ranking family—solve this elegantly in a single, efficient query. In this lesson, you'll learn how to use RANK(), DENSE_RANK(), and ROW_NUMBER() to assign ranks per group, handle ties, and avoid common pitfalls.

The problem this lesson solves

Business reporting often requires ranking rows within partitions: top 10 products, first 3 orders per customer, or student positions per class. Without window functions, you'd reach for a correlated subquery or a self-join, like this:

SELECT product_id, price,
  (SELECT COUNT(*) FROM products p2 WHERE p2.category_id = p1.category_id AND p2.price > p1.price) + 1 AS rank
FROM products p1;

That works on tiny tables but is painful to maintain, slow on real data, and does not handle ties consistently. Window functions let you write the same logic in one pass, keep the query clear, and compute ranks, percentiles, and moving aggregates efficiently.

Core concept / mental model

A window function computes a value across a set of rows related to the current row, without collapsing them into a single output row like a normal aggregate does. You can think of a window as a "sliding frame" defined by three clauses:

• PARTITION BY splits rows into groups (like GROUP BY for ranking context).
• ORDER BY inside the window defines the order within each partition.
• The function (RANK(), DENSE_RANK(), ROW_NUMBER()) then assigns a number to each row.

For ranking specifically, three built-in functions exist:

Picture a classroom test: ROW_NUMBER() gives each student a seat number (random for ties), RANK() gives you competition ranking (two first places, next is third), and DENSE_RANK() gives medals (two golds, next is silver).

How it works step by step

1. Start with a base query that returns the rows you want to rank.
2. Add the window function to the SELECT list, after FROM and WHERE are resolved.
3. Define the window with OVER(): - PARTITION BY column to split into categories. - ORDER BY column to set the order within each partition.
4. Choose the ranking function based on how you want ties handled.
5. Optionally, filter by rank using a subquery or CTE because you can't use window functions directly in WHERE.

Window functions are evaluated after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. This order matters when you filter by rank—you must wrap the query.

Hands-on walkthrough

Let's use a sales table with employee performance data:

CREATE TABLE sales (
  employee_id INT,
  department TEXT,
  amount NUMERIC
);

INSERT INTO sales VALUES
  (1, 'Sales', 1200),
  (2, 'Sales', 1500),
  (3, 'Sales', 1500),
  (4, 'Marketing', 800),
  (5, 'Marketing', 1100),
  (6, 'Marketing', 1100),
  (7, 'Marketing', 900);

Now, compare the three ranking functions for all employees:

SELECT
  employee_id,
  department,
  amount,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS row_num,
  RANK()        OVER (PARTITION BY department ORDER BY amount DESC) AS rank,
  DENSE_RANK()  OVER (PARTITION BY department ORDER BY amount DESC) AS dense_rank
FROM sales
ORDER BY department, amount DESC;

Output (abbreviated):

 employee_id | department | amount | row_num | rank | dense_rank
-------------+------------+--------+---------+------+------------
           2 | Sales      |   1500 |       1 |    1 |          1
           3 | Sales      |   1500 |       2 |    1 |          1
           1 | Sales      |   1200 |       3 |    3 |          2
           5 | Marketing  |   1100 |       1 |    1 |          1
           6 | Marketing  |   1100 |       2 |    1 |          1
           7 | Marketing  |    900 |       3 |    3 |          2
           4 | Marketing  |    800 |       4 |    4 |          3

Notice how ties give different numbers—use RANK() when you want competition-style gaps, DENSE_RANK() when you want compact rankings.

Top 2 per department using a CTE:

WITH ranked AS (
  SELECT
    employee_id,
    department,
    amount,
    RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rnk
  FROM sales
)
SELECT employee_id, department, amount
FROM ranked
WHERE rnk <= 2
ORDER BY department, rnk;

Output:

 employee_id | department | amount
-------------+------------+--------
           2 | Sales      |   1500
           3 | Sales      |   1500
           5 | Marketing  |   1100
           6 | Marketing  |   1100

Get top 3 with ties using DENSE_RANK:

WITH ranked AS (
  SELECT
    employee_id,
    department,
    amount,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rnk
  FROM sales
)
SELECT employee_id, department, amount
FROM ranked
WHERE rnk <= 3;

This returns all employees in the top 3 dense ranks, including ties, which is often desired for medal-style reporting.

Rank across all rows without partitioning (global leaderboard):

SELECT
  employee_id,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS global_rank
FROM sales
ORDER BY amount DESC;

Compare options / when to choose what

Choosing the right function depends on your business rules:

The main trade-off is gap behavior. If you need to show "1, 2, 2, 4" in a leaderboard, use RANK(). If you need "1, 2, 2, 3" for awarding medals, use DENSE_RANK(). For unique position numbers, use ROW_NUMBER().

Variations and alternatives:

• NTILE(n) divides rows into n buckets, useful for quartile analysis.
• PERCENT_RANK() returns a value between 0 and 1 as the percentage of rows below.
• CUME_DIST() gives cumulative distribution.
• If you need the top N per group, you can also use DISTINCT ON with ORDER BY, but it only returns one row per group and doesn't handle ties like window functions do.

Troubleshooting & edge cases

• Filtering by rank in WHERE: You cannot use WHERE rank <= 3 because window functions are evaluated after WHERE. Use a subquery or CTE.
• Misunderstanding ties: Remember that ROW_NUMBER() does not guarantee which tied row gets which number; the order is arbitrary. Add a tie-breaker column to the ORDER BY for determinism.
• Using PARTITION BY with ORDER BY: If you forget PARTITION BY, you rank globally, which may not be what you want. Conversely, forgetting ORDER BY makes the ranking meaningless (all rows tie).
• NULLs in the ordering column: By default, NULL sorts last in descending order. Use NULLS FIRST or NULLS LAST to control placement.

• Performance: Ranking can be heavy on large tables. Make sure the ORDER BY column is indexed, and you only compute the window on relevant rows (filter first).

• Forgetting the OVER() clause: Using RANK() without OVER() results in a syntax error. Always pair window functions with an empty or defined window.

• Expecting ROW_NUMBER() to be stable: Without a deterministic ORDER BY, the same query can produce different numbers across executions. For reproducible results, add more columns to ORDER BY.

What you learned & what's next

You now know how to use window functions for ranking in PostgreSQL—understanding the difference between ROW_NUMBER(), RANK(), and DENSE_RANK(), and how to apply them with PARTITION BY to rank within groups. You also learned to filter ranked results using CTEs and to choose the right function for your business logic. This skill is essential for writing efficient analytics queries and avoiding slow correlated subqueries.

Next, you'll dive into advanced window frames, where you'll learn to compute running totals, moving averages, and other frame-based calculations—taking your data analysis to the next level.