Window Functions for Ranking

Huda wants to feature the two best sellers from every category in the spring newsletter. You can get revenue per product with GROUP BY. But “the top two within each category” needs something GROUP BY can’t do: rank rows against their neighbours while keeping each row. That’s the job of window functions, and once you have them, a whole class of questions becomes short.

Keeping rows while looking across them

GROUP BY collapses each group into one row. A window function computes a value across a set of rows, the window, and attaches it to every row, collapsing nothing.

The syntax is a function followed by OVER (...). Inside the parentheses, PARTITION BY splits rows into groups, and the calculation restarts in each group; ORDER BY sets the order the function walks through. Leave out PARTITION BY and the whole result is one window.

ROW_NUMBER, RANK and DENSE_RANK

The three ranking functions differ only in how they treat ties. Here are the most frequent UAE buyers of 2025:

WITH uae_orders AS (
  SELECT o.customer_id, COUNT(*) AS orders
  FROM orders AS o
  JOIN customers AS c ON c.customer_id = o.customer_id
  WHERE c.country = 'United Arab Emirates'
    AND o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
)
SELECT
  customer_id,
  orders,
  ROW_NUMBER() OVER (ORDER BY orders DESC, customer_id) AS row_num,
  RANK()       OVER (ORDER BY orders DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY orders DESC) AS dense_rnk
FROM uae_orders
ORDER BY orders DESC, customer_id
LIMIT 8;

Customers 163 and 534 both placed 11 orders. ROW_NUMBER still gives them 4 and 5, unique positions, using customer_id to break the tie. RANK gives both 4 and then jumps to 6, like a sports table. DENSE_RANK gives both 4 and continues with 5.

Pick by the question. “Exactly one row per group” (the latest order, the first visit) is ROW_NUMBER. “Top 3, and include anyone tied for third” is RANK. “The three highest values” is DENSE_RANK.

Top N per group

Window functions run after WHERE, in the SELECT step, so you can’t filter on a rank in the same query’s WHERE. The standard pattern is a CTE that computes the rank and an outer query that filters it:

WITH ranked AS (
  SELECT
    category,
    product,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
)
SELECT category, product, revenue, rnk
FROM ranked
WHERE rnk <= 2;

product_revenue here stands for the per-product revenue CTE you’d write first, using the join from the joins lesson. Building it is the exercise.

First events: one row per customer

The same tool answers a question Huda has wanted for months: which channel brings in new customers? That’s the channel of each customer’s first order, which is one row per partition:

WITH numbered AS (
  SELECT
    customer_id,
    channel,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date, order_id
    ) AS order_seq
  FROM orders
  WHERE status <> 'cancelled'
)
SELECT channel AS first_order_channel, COUNT(*) AS customers
FROM numbered
WHERE order_seq = 1
GROUP BY channel
ORDER BY customers DESC;

Web acquires 268 customers, the app 172 and the marketplace 63. Compare that with total orders by channel and you’ll see whether the marketplace mostly finds new buyers or serves existing ones. order_seq is also useful as a column in its own right: order_seq = 2 finds second orders, the start of every repeat-purchase analysis.

Buckets instead of positions

Sometimes the question isn’t “who is first?” but “which tier is each customer in?” Karim’s finance team splits customers into quartiles by lifetime revenue to compare discount usage across tiers. NTILE(n) deals the ordered rows into n buckets of near-equal size:

WITH lifetime AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
),
tiered AS (
  SELECT customer_id, revenue,
         NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
  FROM lifetime
)
SELECT quartile,
       COUNT(*) AS customers,
       ROUND(MIN(revenue), 2) AS min_revenue,
       ROUND(SUM(revenue), 2) AS revenue
FROM tiered
GROUP BY quartile
ORDER BY quartile;

The top quartile’s revenue dwarfs the bottom one, the familiar shape of almost every customer base. Notice the layering: aggregate to one row per customer, assign a tier with a window function, then group again by tier. Windows and GROUP BY aren’t rivals; most real analyses use both, in separate steps.

NTILE splits by row count, not by value, so customers with identical revenue can land in different buckets. For tiers with fixed thresholds (“over 2,000 is gold”), a CASE expression is the honest tool.

Ranking is one half of window functions. The other half, running totals and comparisons with the previous row, turns a monthly table into a trend, and it’s next.