SQL Window Functions: The Query Feature You're Not Using

Every developer hits the same wall eventually: you need "the latest order per customer," or "the running total," or "the rank of each product in its category," and the query turns into a pile of self-joins, correlated subqueries, and muttered curses. Window functions exist precisely to make these queries trivial — and yet they're the most under-taught feature in SQL. This is the deep dive I wish I'd had: what they are, how OVER() works, and the half-dozen patterns that cover 90% of real uses.

The mental model: GROUP BY collapses, windows don't

GROUP BY takes many rows and returns one row per group. That's its job — and it's why "top N per group" is so awkward: you need the group's rows and the group-level computation together, and GROUP BY throws the rows away.

A window function keeps every row and computes a value across a related set of rows — the window. The syntax is always the same shape:

1
SELECT
2
  order_id,
3
  customer_id,
4
  amount,
5
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
6
FROM orders;

Every row stays, and customer_total repeats the customer's sum on each of their rows. PARTITION BY splits the table into windows; ORDER BY inside OVER() orders rows within each window; SUM(amount) runs over that window instead of the whole table.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

The most famous family. The differences matter:

1
SELECT
2
  name,
3
  score,
4
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,  -- 1,2,3,4 (unique)
5
  RANK()       OVER (ORDER BY score DESC) AS rnk,      -- 1,1,3,4 (gaps)
6
  DENSE_RANK() OVER (ORDER BY score DESC) AS d_rnk     -- 1,1,2,3 (no gaps)
7
FROM leaderboard;

• ROW_NUMBER() — a unique sequential number per row, ties broken arbitrarily (or by your ORDER BY).
• RANK() — ties share a rank, and the next rank skips: two people at #1 means the next is #3.
• DENSE_RANK() — ties share a rank but no gaps: #1, #1, #2.

Pick RANK() for leaderboards ("we're tied for first"), DENSE_RANK() for paginated category lists, ROW_NUMBER() for deduplication.

The dedup pattern everyone needs

De-duplicating a table with duplicates is the single most common real-world window use. Keep one row per entity, prefer the newest:

1
WITH ranked AS (
2
  SELECT *,
3
    ROW_NUMBER() OVER (
4
      PARTITION BY email
5
      ORDER BY created_at DESC
6
    ) AS rn
7
  FROM users
8
)
9
DELETE FROM users
10
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

One pass, no self-join, and it reads like English: "number the rows per email, newest first, then keep only the first."

Top-N-per-group: the pattern that replaces five self-joins

The classic "three most recent orders per customer":

1
WITH ranked AS (
2
  SELECT *,
3
    ROW_NUMBER() OVER (
4
      PARTITION BY customer_id
5
      ORDER BY created_at DESC
6
    ) AS rn
7
  FROM orders
8
)
9
SELECT * FROM ranked WHERE rn <= 3;

Without window functions this is a correlated subquery with a LIMIT per customer, or a lateral join, or — in the bad old days — a cursor. The window version is faster to write, faster to read, and usually faster to run, because the planner can execute the partition + sort once.

LAG and LEAD: look at the neighbors

LAG(col) fetches the previous row's value within the window; LEAD(col) fetches the next. This is the engine of every "change over time" query — month-over-month growth, diff detection, session boundaries:

1
SELECT
2
  month,
3
  revenue,
4
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
5
  revenue - LAG(revenue) OVER (ORDER BY month) AS monthly_delta,
6
  ROUND(
7
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
8
      / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
9
    1
10
  ) AS pct_change
11
FROM monthly_revenue;

The NULLIF matters: the first row has no prev_revenue, so the delta is NULL and dividing by it would blow up. LAG(col, 2) skips two back, and the third argument sets a default: LAG(col, 1, 0).

Running totals and moving averages

Give SUM an ORDER BY inside OVER() and the window becomes a running frame: from the partition start to the current row. That's a running total:

1
SELECT
2
  day,
3
  amount,
4
  SUM(amount) OVER (ORDER BY day) AS running_total
5
FROM daily_sales;

Moving averages use the frame clause explicitly — ROWS BETWEEN N PRECEDING AND CURRENT ROW:

1
SELECT
2
  day,
3
  amount,
4
  ROUND(AVG(amount) OVER (
5
    ORDER BY day
6
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
7
  ), 2) AS seven_day_avg
8
FROM daily_sales;

That ROWS BETWEEN is the frame definition: which rows, relative to the current one, participate. RANGE is the alternative that works on values ("all rows within ±3 of my value") but ROWS is what you want 95% of the time. Frames are where window functions go from "a feature" to "a superpower" — they let you compute exactly the moving window you mean, with no off-by-one.

NTILE: buckets, quartiles, and percentiles

NTILE(n) splits the window into n buckets as evenly as possible and labels each row with its bucket. Instant percentiles:

1
SELECT
2
  customer_id,
3
  lifetime_value,
4
  NTILE(4) OVER (ORDER BY lifetime_value) AS quartile
5
FROM customers;

A/B test assignment, tiering, "top 10% of users" — all one line. For exact percentiles, most databases offer PERCENTILE_CONT / PERCENTILE_DISC as aggregate functions instead.

Window functions vs GROUP BY: when to use which

A good heuristic: if your query needs a subquery in the SELECT list or a GROUP BY with a MAX-join dance, a window function is probably the cleaner tool. They don't replace GROUP BY — they complement it, and the planner is good at combining both in one pass.

Performance notes

Window functions are not free — the planner usually needs a sort per PARTITION BY/ORDER BY pair, and a bad index can turn a sweet query into a temp-table swap. The wins:

• An index on (customer_id, created_at) makes the top-N-per-group and running-total queries read in order instead of sorting.
• ROW_NUMBER dedup runs in a single pass; the old self-join approach did N passes.
• Frames bounded by ROWS BETWEEN let engines use a sliding window in memory; unbounded frames accumulate.

Where to go from here

Window functions are the rare feature where learning six keywords (OVER, PARTITION BY, ROW_NUMBER, LAG, NTILE, ROWS BETWEEN) unlocks a whole class of queries. Next time you reach for a correlated subquery, pause and ask: "is this just a window?" Half the time, it is — and the query that results will be shorter, clearer, and faster than the one you were about to write.