How to Rank Rows Per Group With SQL Window Functions

Why correlated subqueries fall apart

The naive approach to "top 3 customers per region" runs a subquery for each row:

SELECT region, name, total_spend
FROM customer_totals c
WHERE (
  SELECT COUNT(*) FROM customer_totals c2
  WHERE c2.region = c.region AND c2.total_spend > c.total_spend
) < 3
ORDER BY region, total_spend DESC;

This re-scans the table once per row - O(n^2) in the worst case. With thousands of customers it becomes painfully slow, and the logic is hard to follow.

ROW_NUMBER() OVER (PARTITION BY ...) - the right tool

Window functions compute a value across a set of rows related to the current row, without collapsing them the way GROUP BY does. ROW_NUMBER() assigns 1, 2, 3, ... within each partition - the partition being your group key.

The two-CTE pattern is the standard structure:

-- Step 1: aggregate per customer first
WITH per_customer AS (
  SELECT
    c.region,
    c.id            AS customer_id,
    c.name          AS customer_name,
    COALESCE(SUM(o.amount), 0) AS total_spend
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.region, c.id, c.name
),
-- Step 2: rank within each region
ranked AS (
  SELECT
    region,
    customer_id,
    customer_name,
    total_spend,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY total_spend DESC, customer_id   -- tiebreak for determinism
    ) AS rank
  FROM per_customer
)
-- Step 3: keep only the top 3 per region
SELECT region, customer_id, customer_name, total_spend, rank
FROM ranked
WHERE rank <= 3
ORDER BY region, rank;

Why the two-CTE structure is necessary

You cannot filter on a window function alias directly in a WHERE clause - window functions are evaluated after WHERE. Wrapping the ranking step in a CTE (or subquery) and filtering in the outer query is the correct pattern:

-- This DOES NOT WORK - rank is not yet available here:
SELECT ..., ROW_NUMBER() OVER (...) AS rank
FROM per_customer
WHERE rank <= 3;  -- ERROR: column "rank" does not exist

ROW_NUMBER vs RANK vs DENSE_RANK

For top-N reports where you want exactly N rows per partition, ROW_NUMBER is the safest choice. Add a stable tiebreak column (customer_id) in the ORDER BY to make results deterministic across runs.

Performance

The two-CTE version does one full scan of orders, one aggregation, and one ranking pass - O(n log n) with an index on customer_id. Modern query planners (PostgreSQL, Snowflake, BigQuery, DuckDB) all handle this in a single execution plan, making window function queries significantly faster than correlated subquery equivalents on large datasets.