SQL Window Functions: The Feature That Changed Everything

Before window functions, SQL was good at two things: fetching rows and aggregating rows. You could get all sales, or you could get the total sales. But getting each sale alongside the running total? That required gnarly self-joins or correlated subqueries that made your eyes bleed.

Window functions let you compute aggregates, rankings, and comparisons across related rows -- without collapsing everything into a single result. The rows stay intact. The computation just rides alongside.

The Syntax

Every window function follows this pattern:

function_name() OVER (
  PARTITION BY column    -- optional: split into groups
  ORDER BY column        -- optional: define row order
  ROWS BETWEEN ...       -- optional: define the frame
)

OVER() is what makes it a window function. Without OVER(), SUM() is a regular aggregate that collapses rows. With OVER(), it computes across a "window" of rows and returns a value for each row.

Ranking Functions

ROW_NUMBER, RANK, DENSE_RANK

Given this sales table:

| rep     | region | amount |
|---------|--------|--------|
| Anika   | East   | 50000  |
| Ben     | East   | 50000  |
| Clara   | East   | 35000  |
| Dani    | West   | 70000  |
| Eli     | West   | 45000  |

SELECT
  rep,
  region,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
  RANK()       OVER (ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales;

| rep   | region | amount | row_num | rnk | dense_rnk |
|-------|--------|--------|---------|-----|-----------|
| Dani  | West   | 70000  | 1       | 1   | 1         |
| Anika | East   | 50000  | 2       | 2   | 2         |
| Ben   | East   | 50000  | 3       | 2   | 2         |
| Eli   | West   | 45000  | 4       | 4   | 3         |
| Clara | East   | 35000  | 5       | 5   | 4         |

The difference shows up at ties:

• ROW_NUMBER: always unique. Ties get arbitrary ordering (Anika vs Ben could swap)


• RANK: ties get the same rank, then skips. 2, 2, 4 (no 3)


• DENSE_RANK: ties get the same rank, no skipping. 2, 2, 3

NTILE: Split Into Buckets

SELECT rep, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales;

Divides rows into N roughly equal groups. Useful for "top 25% of customers" type analysis.

PARTITION BY: Groups Within Groups

PARTITION BY is like GROUP BY for window functions. It splits the data into independent windows.

SELECT
  rep,
  region,
  amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;

| rep   | region | amount | region_rank |
|-------|--------|--------|-------------|
| Anika | East   | 50000  | 1           |
| Ben   | East   | 50000  | 1           |
| Clara | East   | 35000  | 3           |
| Dani  | West   | 70000  | 1           |
| Eli   | West   | 45000  | 2           |

Each region gets its own ranking. This is how you solve "top N per group" -- one of the most common real-world SQL problems.

Top seller per region:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
)
SELECT rep, region, amount
FROM ranked
WHERE rn = 1;

LAG and LEAD: Peeking at Other Rows

LAG looks at a previous row. LEAD looks at the next one. Both take the column name and an offset (default 1).

SELECT
  month,
  revenue,
  LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

| month   | revenue | prev_month | mom_change |
|---------|---------|------------|------------|
| 2026-01 | 120000  | NULL       | NULL       |
| 2026-02 | 135000  | 120000     | 15000      |
| 2026-03 | 128000  | 135000     | -7000      |

NULL in the first row because there's no previous month. You can provide a default value as a third argument: LAG(revenue, 1, 0).

Year-over-year comparison:

SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
  ROUND(
    (revenue - LAG(revenue, 12) OVER (ORDER BY month))
    / LAG(revenue, 12) OVER (ORDER BY month) * 100, 1
  ) AS yoy_pct
FROM monthly_revenue;

Aggregate Window Functions

SUM, AVG, COUNT, MIN, MAX all work as window functions when you add OVER().

Running total

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

| order_date | amount | running_total |
|------------|--------|---------------|
| 2026-01-05 | 250    | 250           |
| 2026-01-12 | 175    | 425           |
| 2026-01-20 | 300    | 725           |
| 2026-02-01 | 150    | 875           |

Moving average

SELECT
  order_date,
  amount,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM orders;

That ROWS BETWEEN clause is the frame specification. It defines exactly which rows are included in the window calculation.

Frame Specifications

The frame defaults depend on context, which trips people up:

• With ORDER BY: default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (everything up to the current row)
• Without ORDER BY: default frame is the entire partition

You can override it:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- running total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW            -- 3-row moving window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING             -- centered 3-row window
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING     -- current row to the end

ROWS counts individual rows. RANGE groups rows with the same ORDER BY value together (matters when you have ties). For most practical work, ROWS is what you want.

Finding Duplicates

One of the most practical uses of window functions:

WITH dupes AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM users
)
SELECT * FROM dupes WHERE rn > 1;

This finds all duplicate email rows except the first occurrence. Change the ORDER BY to control which one counts as the "original." Switch WHERE rn > 1 to DELETE FROM dupes WHERE rn > 1 (in databases that support CTE deletes, like PostgreSQL) to clean them up.

Performance Notes

Window functions run after WHERE, GROUP BY, and HAVING -- they operate on the result set, not the raw table. This means you can't filter on a window function result in the same WHERE clause. Wrap it in a CTE or subquery, as shown in the top-N example above.

Indexing the ORDER BY columns in your OVER() clause helps with large datasets. If you're partitioning and ordering, a composite index on (partition_column, order_column) is ideal.

Go Write Some Queries

Window functions are one of those SQL features where the syntax looks intimidating but the concept is straightforward once you've written five or six queries. CodeUp has SQL challenges that cover ranking, running totals, and LAG/LEAD comparisons -- you write the query, run it against real data, and see the results immediately. That feedback loop is how this stuff sticks.