Window Functions

Window functions perform calculations across a set of rows related to the current row — without collapsing them into a single output row the way GROUP BY does. They’re the tool that makes it possible to compute “salary vs. department average” or “this month’s revenue vs. last month’s” in a single query, keeping all individual rows visible. They’re one of the most powerful SQL features and are tested heavily in interviews precisely because they solve problems that have no clean alternative.

The OVER Clause

Every window function uses an OVER clause that defines the “window” of rows it sees. An empty OVER() means the window is the entire result set — every row sees the same aggregate value from all rows.

SELECT
  name,
  salary,
  AVG(salary) OVER () AS company_avg   -- the window is all rows in the result
FROM employees;
-- Each row shows its own salary AND the overall company average alongside it

PARTITION BY

PARTITION BY divides rows into groups (similar to GROUP BY), but keeps all rows in the output rather than collapsing them. This is the key difference: you get summary statistics per group without losing the detail rows.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  -- How much does this employee earn above or below their department average?
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Each row shows its own salary alongside the department average — something impossible with plain GROUP BY, which would require a separate query or a self-join.

Ranking Functions

Ranking functions assign a position number to each row within its partition. They’re essential for “top N per group” problems and for ranking competitors, products, or employees by any metric.

ROW_NUMBER

ROW_NUMBER assigns a unique sequential integer to each row within the partition, with no ties. Even if two rows have identical values, they get different numbers.

SELECT
  name,
  department,
  salary,
  -- Number employees within each department by salary, highest first
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

RANK vs DENSE_RANK

RANK and DENSE_RANK both handle ties, but differ in how they number the positions after a tie. RANK skips numbers (like an Olympic podium where two silver medals means no bronze), while DENSE_RANK never skips.

SELECT
  name,
  salary,
  RANK()       OVER (ORDER BY salary DESC) AS rank,        -- 1, 2, 2, 4 (skips 3)
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank   -- 1, 2, 2, 3 (no gap)
FROM employees;

Use DENSE_RANK when gaps in ranking numbers would be confusing — for example, showing “3rd place” when there is no “3rd” feels wrong.

Top-N Per Group Pattern

Finding the top N records per group is one of the most common window function interview problems. The pattern: rank within the group, then filter in an outer query.

-- Top 3 earners per department — the classic window function interview question
WITH ranked AS (
  SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk <= 3;  -- can't filter on window function result directly in WHERE

NTILE

NTILE(n) divides rows into n roughly equal buckets and assigns each row a bucket number. It’s useful for segmenting customers into quartiles, deciles, or percentile groups for analysis.

-- Assign each customer to a revenue quartile (1 = lowest, 4 = highest)
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM customer_totals;

LAG and LEAD

LAG and LEAD access values from other rows relative to the current row — LAG looks backward, LEAD looks forward. They eliminate the need for self-joins when computing period-over-period comparisons, which makes month-over-month or day-over-day metrics straightforward.

SELECT
  month,
  revenue,
  LAG(revenue)  OVER (ORDER BY month) AS prev_month_revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue;

Month-over-Month Growth Rate

SELECT
  month,
  revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),  -- guard against division by zero
    2
  ) AS growth_pct
FROM monthly_revenue;

FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE return the first or last value in the window frame. They’re useful for comparing each row to the group’s best or worst value. LAST_VALUE requires an explicit frame clause because the default frame only extends to the current row.

-- Compare each employee's salary to the highest earner in their department
SELECT
  name,
  department,
  salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_earner
FROM employees;

-- LAST_VALUE needs an explicit unbounded frame to see all rows in the partition
LAST_VALUE(name) OVER (
  PARTITION BY department
  ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_earner

Running Totals and Moving Averages

Window functions with frame clauses are how you compute running totals and moving averages. The frame clause (ROWS BETWEEN ...) specifies exactly which rows the window includes for each position.

SELECT
  created_at::DATE AS day,
  daily_revenue,
  -- Running total: cumulative sum from the first row to the current row
  SUM(daily_revenue) OVER (ORDER BY created_at) AS running_total,
  -- 7-day moving average: current row plus the 6 preceding rows
  AVG(daily_revenue) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM daily_stats;

Named Windows

If you use the same OVER definition multiple times in one query, name it with a WINDOW clause at the bottom. This avoids repetition and makes it easier to change the window definition in one place.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense_rnk
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);
-- Change the partition or sort here once instead of in every OVER clause

Key Differences from GROUP BY

Understanding when to use window functions versus GROUP BY is fundamental. The table below captures the decision:

Window functions never reduce the number of rows. When you need both individual row data and summary statistics in the same query, window functions are the right tool.