How to Build a Top-N Report With MySQL Window Functions

Why the correlated-subquery version falls apart

The classic "top 3 customers per region" report is often written with a subquery that counts how many customers in the same region outspent the current one:

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 totals once per row - O(n^2) in the worst case - and the intent is buried in the subquery. Window functions replace the whole thing with one declarative ranking pass.

ROW_NUMBER() OVER (PARTITION BY ...)

A window function computes 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, ... inside each partition, and the partition is your group key - here, region.

You cannot filter on a window function alias in a WHERE clause, because window functions are evaluated after WHERE. So the standard shape is a two-CTE pattern: aggregate first, rank second, then filter in an outer query.

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
),
ranked AS (
  SELECT
    region,
    customer_id,
    customer_name,
    total_spend,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY total_spend DESC, customer_id   -- stable tiebreak
    ) AS rank
  FROM per_customer
)
SELECT region, customer_id, customer_name, total_spend, rank
FROM ranked
WHERE rank <= 3
ORDER BY region, rank;

Two things make this correct. LEFT JOIN orders keeps customers who have no orders (their total_spend becomes 0 via COALESCE), and the customer_id tiebreak in the ORDER BY makes the ranking deterministic when two customers tie on spend.

Run it and check the output

On the seeded data - 12 customers across the east, west, and central regions - the query returns exactly 9 rows, three per region, ranked 1 to 3:

psql -h localhost -U postgres -d app -f query.sql

The rank-1 rows should be Carol (east, 1000.00), Grace (west, 1500.00), and Judy (central, 1000.00). If you get more than 9 rows you probably forgot the WHERE rank <= 3 filter; if a region is missing a customer, your JOIN dropped the zero-spend rows.

MySQL, PostgreSQL, and the rest

This is standard SQL:2003 window syntax, so the exact same query runs on MySQL 8.0+, PostgreSQL, Snowflake, BigQuery, and DuckDB. The only version gotcha is that MySQL added window functions in 8.0 - on 5.7 and earlier they do not exist, which is the most common reason ROW_NUMBER() OVER (...) throws a syntax error on MySQL.

ROW_NUMBER vs RANK vs DENSE_RANK

For a top-N report where you want exactly N rows per group, ROW_NUMBER is the safe choice - RANK can return more than N rows when there are ties at the cutoff.