Window functions and row_number

Window functions are the most powerful analytical tool in SQL. Unlike GROUP BY which collapses rows into one per group, window functions add a calculated column to every row without losing any detail.

Group by vs window functions

GROUP BY collapses rows into one per group, losing individual row detail. Window functions let you rank and aggregate while keeping every row visible. For example, you can show each order item alongside its rank within the order, something GROUP BY cannot do.

window_anatomy.sql
sql
-- Window function anatomy:
-- function() OVER (PARTITION BY ... ORDER BY ...)
SELECT
  seller_id,
  price,
  SUM(price) OVER () AS total_revenue,
  SUM(price) OVER (PARTITION BY seller_id) AS seller_revenue
FROM order_items
LIMIT 20;

-- OVER () = entire result set as one window
-- PARTITION BY = separate window per group

OVER() defines the window. Empty OVER() means all rows. PARTITION BY creates sub-windows. Every row keeps its detail plus the window calculation.

Now that you understand the OVER() clause, let us look at ranking functions. ROW_NUMBER assigns a unique sequential number to each row within the window.

ranking.sql
sql
-- ROW_NUMBER: unique rank per seller by revenue
WITH seller_revenue AS (
  SELECT seller_id, SUM(price) AS revenue
  FROM order_items
  GROUP BY seller_id
)
SELECT
  seller_id,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rank
FROM seller_revenue
LIMIT 10;

ROW_NUMBER assigns a unique sequential number. No ties, so even identical values get different numbers.

Adding PARTITION BY to a ranking function restarts the numbering for each group. This lets you rank items within each order separately.

partition_rank.sql
sql
-- ROW_NUMBER within each order: rank items by price
SELECT
  order_id,
  product_id,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY price DESC
  ) AS item_rank
FROM order_items
LIMIT 20;

PARTITION BY order_id restarts numbering for each order. This ranks items within each order by price.

Quiz: Quiz

Loading practice…