SQL Window Functions: RANK, ROW_NUMBER, PARTITION BY

Window functions are what separate junior from senior SQL developers. Master RANK, DENSE_RANK, ROW_NUMBER, and OVER() in one read.

Window functions are the most powerful — and most underused — feature in SQL. They let you perform calculations across a set of rows that are related to the current row, without collapsing the result set the way GROUP BY does. If GROUP BY still feels shaky, start with GROUP BY & aggregate functions first. The SQL cheat sheet has a dedicated window functions section with copy-ready RANK, ROW_NUMBER, and PARTITION BY examples.

What are Window Functions?

A window function computes a value for each row based on a "window" of related rows. Unlike GROUP BY, the rows stay visible. You get the aggregate result and the original row data in the same query.

PARTITION BY category — Every input row is kept — the window function adds a value computed within each colored partition. GROUP BY, by contrast, would collapse each group down to a single row.

-- Without window functions (GROUP BY collapses rows)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- With window functions (rows stay, avg is added per row)
SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

The OVER() Clause

Every window function uses OVER() to define its window. An empty OVER() means "the entire result set". Add PARTITION BY or ORDER BY inside to narrow or order the window.

-- Running count over entire table
SELECT name, COUNT(*) OVER () AS total_rows
FROM users;

PARTITION BY

PARTITION BY divides the result set into groups. The window function restarts for each partition — like GROUP BY, but without collapsing rows.

-- Rank employees by salary within each department
SELECT
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

ROW_NUMBER

ROW_NUMBER assigns a unique sequential integer to each row within a partition. Ties get different numbers — they're assigned arbitrarily by the engine.

-- Get the first order per user (deduplicate)
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at
    ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

RANK vs DENSE_RANK

Both handle ties, but differently. RANK leaves gaps after ties. DENSE_RANK doesn't. For leaderboards where you want positions 1, 2, 2, 3 use DENSE_RANK.

SELECT
  name,
  score,
  RANK()       OVER (ORDER BY score DESC) AS rank_with_gaps,  -- 1,2,2,4 (gaps)
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank       -- 1,2,2,3 (no gap)
FROM leaderboard;

-- score 100: rank=1, dense_rank=1
-- score 95:  rank=2, dense_rank=2
-- score 95:  rank=2, dense_rank=2  (tie)
-- score 90:  rank=4, dense_rank=3  (RANK skips 3, DENSE_RANK doesn't)

LEAD & LAG

LAG accesses values from previous rows. LEAD accesses values from following rows. Both are essential for comparing rows to their neighbors — period-over-period comparisons, streak detection, and gap analysis.

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Running Totals

Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to get a cumulative sum. This is the standard pattern for running totals and rolling windows.

-- Cumulative revenue by day
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

Practical Examples

• Top N per group: ROW_NUMBER() + CTE + WHERE rn <= N
• Percentile ranking: PERCENT_RANK() or NTILE(100)
• Moving average: AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
• Find gaps/islands: ROW_NUMBER() - ROW_NUMBER() OVER (PARTITION BY group ORDER BY seq)
• Year-over-year comparison: LAG(value, 12) OVER (ORDER BY month)