Window functions — ROW_NUMBER, RANK, and running totals

Window Functions Aggregate Without Collapsing Rows

GROUP BY collapses rows into one per group. Window functions compute across a set of rows and keep every row. This is the key difference.

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY created_at)           AS global_rank,
  ROW_NUMBER() OVER (PARTITION BY customer_id
                     ORDER BY created_at)            AS per_customer_rank,
  SUM(amount)  OVER (ORDER BY created_at
                     ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW)                AS running_total
FROM orders;

PARTITION BY Is GROUP BY for Window Scope

PARTITION BY splits rows into independent windows. The function restarts for each partition. Without PARTITION BY, the whole result set is one window.

ROW_NUMBER vs RANK vs DENSE_RANK

When values tie, these three functions behave differently:

-- Scores: 100, 100, 90
ROW_NUMBER: 1, 2, 3   -- no ties, arbitrary order
RANK:       1, 1, 3   -- gap after tie
DENSE_RANK: 1, 1, 2   -- no gap

Use RANK for leaderboards where gaps communicate skipped positions. Use DENSE_RANK when you want sequential tiers regardless of ties.