What are window functions?

Question

Accepted Answer

**Window functions** perform calculations across a set of rows (a "window") **related to the current row**, *without collapsing them into a single output row* (unlike aggregates with `GROUP BY`). They enable powerful analytics — running totals, rankings, comparisons to other rows — while keeping each row in the result.

## The key difference from GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Ranking functions

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

The `OVER (...)` clause defines the window. **`PARTITION BY`** divides rows into groups (like GROUP BY, but per-row), and **`ORDER BY`** orders within each partition. `ROW_NUMBER`/`RANK`/`DENSE_RANK` assign rankings.

## Aggregate window functions (running totals, per-group aggregates)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Aggregates as window functions compute running totals, moving averages, or per-partition aggregates while keeping each row — impossible with GROUP BY alone.

## LAG/LEAD — compare to other rows

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** access values from previous/next rows — perfect for period-over-period comparisons (month-over-month growth, etc.).

## Why it matters

Window functions are a powerful, advanced SQL feature essential for **analytical queries**, and understanding them is valuable senior-level knowledge for data analysis, reporting, and complex querying.

Their defining capability — performing calculations across related rows **while keeping every row** (unlike GROUP BY, which collapses rows) — enables a whole class of analytics that's difficult or impossible otherwise: **rankings** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — finding top N per group, assigning ranks), **running totals and moving averages** (cumulative sums, per-partition aggregates shown alongside each row), and **row-to-row comparisons** (`LAG`/`LEAD` for period-over-period analysis like month-over-month growth).

These are common, valuable analytical needs (leaderboards, cumulative metrics, trend analysis, comparing each row to its group's average) that window functions handle elegantly in a single query, where pre-window-function SQL required complex self-joins or subqueries.

Understanding the `OVER (PARTITION BY ... ORDER BY ...)` syntax (defining the window), the ranking functions, aggregate window functions (running totals, per-partition aggregates), and `LAG`/`LEAD` (accessing other rows) covers the powerful analytical toolkit.

Since data analysis and sophisticated reporting are important uses of SQL, and since window functions are the modern, powerful way to express rankings, running calculations, and row comparisons (significantly more capable and elegant than alternatives), mastering window functions is valuable, frequently-applied knowledge for analytical SQL that distinguishes developers who can write sophisticated data queries — a hallmark of advanced SQL skill and a common topic in data-focused roles and interviews.