SQL Window Functions

TL;DR: A window function runs a calculation over a window of rows defined by OVER (PARTITION BY ... ORDER BY ...) while keeping every input row in the output. That single property, no collapsing, is what separates it from GROUP BY and unlocks ranking, row-to-row comparison, and running totals in one pass.

The idea

Read step 6 against what GROUP BY would do at the same point. That single difference, attaching instead of collapsing, is the whole reason window functions exist.

GROUP BY answers "one number per group" and throws the detail rows away. A window function answers "for each row, what does this group look like around it" and keeps the row. You attach an OVER clause to an aggregate or a ranking function, and SQL evaluates that function against a slice of the result set instead of the whole table.

The OVER clause has three knobs. PARTITION BY splits rows into independent groups (like a GROUP BY that does not collapse). ORDER BY orders rows inside each partition, which is what makes "previous row" and "running total" meaningful. The frame (ROWS BETWEEN ...) bounds how many rows the function sees relative to the current one.

The three ranking functions trip people up. Given values 100, 100, 90: ROW_NUMBER gives 1, 2, 3 (arbitrary tie-break, always unique). RANK gives 1, 1, 3 (ties share a rank, then it skips). DENSE_RANK gives 1, 1, 2 (ties share, no gap). Pick ROW_NUMBER for "give me the single latest row per customer", RANK/DENSE_RANK for leaderboards.

LAG and LEAD pull a value from a row N positions back or ahead in the partition, the clean way to compute deltas (today versus yesterday) without a self-join.

A worked example

Rank each customer's orders by amount, and carry a running total of their spend over time.

SELECT
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY amount DESC
  ) AS amount_rank,
  SUM(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  amount - LAG(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS delta_vs_prev
FROM orders;

Every original order row survives. amount_rank orders within the customer by size; running_total accumulates by date; delta_vs_prev is null on a customer's first order because there is no prior row. Note the explicit frame on the running total: with ORDER BY and no frame, the default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties in order_date as one bucket and can quietly inflate the total. Spelling out ROWS is the safer habit. Swap to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and the same SUM becomes a trailing 7-row moving average when you divide by the count.

Why interviewers probe this

It is the fastest read on real SQL fluency. Someone who reaches for a correlated subquery or a self-join to find "the top order per customer" is telling you they have not used a window function in anger. The follow-up is usually "now do it without dropping the other rows", which is exactly the case GROUP BY cannot handle and a window can. A second probe: "what is the difference between RANK and DENSE_RANK, and which one for a top-3-with-ties report?" If you say RANK, you may return more than three rows when ties straddle the cutoff, so the honest answer names the trade.

Common misconceptions

• "A window function is just a fancy GROUP BY." They compute differently: GROUP BY collapses, windows preserve. You can use both in one query, but the window runs after the GROUP BY and WHERE, on the already-grouped rows.
• "I can filter on a window result in WHERE." You cannot. Window functions are evaluated after WHERE, so to filter on amount_rank = 1 you wrap the query in a CTE or subquery and filter on the outer level.
• "ORDER BY in OVER sorts my output." It only orders rows inside the window for the calculation. Your final row order still needs a top-level ORDER BY.
• "Leaving out the frame is harmless." With an ORDER BY present, the default frame is RANGE, not ROWS, and it lumps tied order keys together. For running totals, state ROWS explicitly.

Key takeaways

• OVER (PARTITION BY ... ORDER BY ...) computes per-window values while keeping every row; that is the whole point versus GROUP BY.
• ROW_NUMBER is always unique, RANK skips after ties, DENSE_RANK does not; choose by whether you need uniqueness or true ranking.
• LAG/LEAD replace self-joins for row-to-row deltas; running totals and moving averages come from an explicit ROWS BETWEEN frame.
• You cannot filter a window result in WHERE; push it to an outer query or CTE.