Blog · 2026-02-10 · 11 min read

SQL Window Functions

The window-function questions that separate analysts who can query from analysts who can answer questions, ranking functions, running totals, frames, LAG and LEAD, NTILE and the filters that catch people out.

Window functions compute across a set of rows related to the current row without collapsing the result the way GROUP BY does. They are the difference between running a query and answering a question. The SQL here targets PostgreSQL; window functions are standard since SQL:2003 and are available in MySQL 8.0 and later, SQLite 3.25 and later, SQL Server, Oracle, Snowflake, BigQuery and Databricks, but the frame syntax and null-ordering defaults vary, so check yours.

The schema we will use

CREATE TABLE reps (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE sales (
  id      integer PRIMARY KEY,
  rep_id  integer NOT NULL REFERENCES reps (id),
  region  text NOT NULL,
  amount  numeric(12, 2) NOT NULL,
  sold_at timestamptz NOT NULL
);

CREATE INDEX sales_region_sold_at_idx ON sales (region, sold_at);

Keeping the schema fixed makes the differences between the answers obvious, which is exactly what an interviewer is doing when they ask you to build queries one after another.

What is the actual difference from GROUP BY?

GROUP BY reduces many rows to one row per group, which means any detail not in the grouping key or an aggregate is gone. A window function keeps every row and adds a computed column alongside it, computed from the other rows in the same partition.

-- One row per region. Per-sale detail is gone.
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;

-- Every sale, plus its region's total repeated on each row.
SELECT s.id, s.rep_id, s.region, s.amount, s.sold_at,
       SUM(s.amount) OVER (PARTITION BY s.region) AS region_total
FROM sales s;

If the window's OVER clause has no ORDER BY, the default frame is the entire partition, so the second query gives every sale in a region the same total. That is the distinction to say out loud: aggregation collapses, windowing annotates.

The three clauses inside OVER are PARTITION BY to define the group of related rows, ORDER BY to sequence them, and an optional frame to say which of the partitioned rows the function actually sees.

ROW_NUMBER, RANK, and DENSE_RANK

These three are identical unless there are ties, and the ties are the whole point of the question. Use data where two reps genuinely tie, because that is where picking the wrong one produces a wrong answer.

SELECT
  s.region,
  r.name,
  s.amount,
  ROW_NUMBER() OVER (PARTITION BY s.region ORDER BY s.amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY s.region ORDER BY s.amount DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY s.region ORDER BY s.amount DESC) AS dense_num
FROM sales s
JOIN reps r ON r.id = s.rep_id
ORDER BY s.region, s.amount DESC, r.name;

Suppose the top two sales in EMEA are both 5000.00. ROW_NUMBER gives 1 and 2, and which rep gets which is arbitrary. RANK gives 1 and 1, then skips to 3. DENSE_RANK gives 1 and 1, then 2.

Use ROW_NUMBER when you need exactly one row per group, such as de-duplicating to the latest row per customer, because exactly one winner survives. Use RANK or DENSE_RANK for a leaderboard, where showing a tie as a tie is the honest answer and the gap after a tie is meaningful information.

Running totals, and why the frame clause is not optional

SELECT
  s.id,
  s.sold_at,
  s.amount,
  SUM(s.amount) OVER (
    ORDER BY s.sold_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales s
ORDER BY s.sold_at;

The frame clause is what makes this a running total. Drop it and, because ORDER BY is present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is peer-based. With a sold_at precise to the microsecond, ties are rare and you will never notice the difference, which is why this bug ships.

Force the tie so it is visible. Two sales at exactly the same timestamp:

SELECT
  s.id,
  s.sold_at,
  s.amount,
  SUM(s.amount) OVER (ORDER BY s.sold_at) AS default_range_frame,
  SUM(s.amount) OVER (
    ORDER BY s.sold_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS explicit_rows_frame
FROM sales s
ORDER BY s.sold_at;

The default_range_frame column reads 200 on both rows, which is not a running total at all; the explicit_rows_frame column reads 100 then 200. RANGE includes every row that ties with the current row on the ORDER BY key, so at the first of two tied rows it has already swallowed the second. ROWS counts physical row position and never does that. GROUPS is the third option, counting peers as a group, available in PostgreSQL 11 and later.

LAG and LEAD for period-over-period comparison

LAG reads backwards, LEAD reads forwards, and together they replace a self-join. Naming the window once keeps the query readable and stops you from repeating a long partition clause:

SELECT
  s.region,
  r.name,
  s.sold_at::date AS sold_on,
  s.amount,
  LAG(s.amount)  OVER w AS prev_amount,
  LEAD(s.amount) OVER w AS next_amount
FROM sales s
JOIN reps r ON r.id = s.rep_id
WINDOW w AS (PARTITION BY s.rep_id ORDER BY s.sold_at)
ORDER BY s.region, r.name, s.sold_at;

The classic follow-up is period-over-period growth. Two things to get right: the first row has no predecessor, so LAG returns null and that null propagates through the arithmetic; and a previous value of zero makes the division blow up.

SELECT
  s.id,
  s.sold_at::date AS sold_on,
  s.amount,
  LAG(s.amount) OVER (ORDER BY s.sold_at) AS prev_amount,
  ROUND(
    100.0 * (s.amount - LAG(s.amount) OVER (ORDER BY s.sold_at))
      / NULLIF(LAG(s.amount) OVER (ORDER BY s.sold_at), 0),
    2
  ) AS pct_change
FROM sales s
ORDER BY s.sold_at;

NULLIF(..., 0) turns a zero divisor into null, which yields a null percentage instead of a division error. If you need the previous value but not the null-propagation, use the third argument of LAG, which is the offset, combined with a frame, or guard the arithmetic with COALESCE.

NTILE for bucketing

NTILE(n) splits each partition into n roughly equal groups in order, which is how you get quartiles, quintiles and deciles without hard-coding thresholds.

SELECT
  r.name,
  s.region,
  s.amount,
  NTILE(4) OVER (PARTITION BY s.region ORDER BY s.amount DESC) AS quartile,
  NTILE(10) OVER (PARTITION BY s.region ORDER BY s.amount DESC) AS decile
FROM sales s
JOIN reps r ON r.id = s.rep_id;

Be honest about the two caveats, because they are what a follow-up question will probe. NTILE distributes rows, not value ranges, so buckets are equal in row count but not in width, and a boundary that falls between two tied rows splits the tie arbitrarily. And with fewer rows than buckets, the tail buckets are empty, so NTILE(4) over three rows gives one row per bucket for the first three and nothing in the fourth.

FIRST_VALUE and LAST_VALUE, and the frame that bites everyone

FIRST_VALUE works out of the box. LAST_VALUE does not, because the default frame stops at the current row.

-- LAST_VALUE with the default frame is just the current row.
SELECT s.id, s.amount,
       FIRST_VALUE(s.amount) OVER (ORDER BY s.id) AS first_value,
       LAST_VALUE(s.amount)  OVER (ORDER BY s.id) AS last_value_bug
FROM sales s
ORDER BY s.id;

-- Running last value: the most recent value up to and including this row.
SELECT s.id, s.amount,
       LAST_VALUE(s.amount) OVER (
         ORDER BY s.id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_last
FROM sales s
ORDER BY s.id;

-- Last value in the whole partition: needs UNBOUNDED FOLLOWING.
SELECT s.id, s.amount,
       LAST_VALUE(s.amount) OVER (
         ORDER BY s.id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS partition_last
FROM sales s
ORDER BY s.id;

The asymmetry is worth stating because it explains the whole bug: UNBOUNDED PRECEDING is already in the default frame, so FIRST_VALUE is correct by accident, while CURRENT ROW is the end of the default frame, so LAST_VALUE silently gives you the current row.

Sliding windows

ROWS BETWEEN n PRECEDING AND CURRENT ROW gives a moving aggregate. A frame that ends before the current row gives you the trailing comparison, which is a different and often more useful thing.

SELECT
  s.rep_id,
  s.sold_at,
  s.amount,
  SUM(s.amount) OVER (
    PARTITION BY s.rep_id
    ORDER BY s.sold_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS last_3_total,
  AVG(s.amount) OVER (
    PARTITION BY s.rep_id
    ORDER BY s.sold_at
    ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING
  ) AS prior_7_avg,
  COUNT(*) OVER (PARTITION BY s.rep_id) AS rep_sales_count
FROM sales s
ORDER BY s.rep_id, s.sold_at;

Near the start of a partition the frame is simply truncated, so last_3_total on the first row is that row alone. That is correct behaviour and worth naming, because a colleague seeing a smaller first value will assume a bug.

A COUNT(*) OVER (PARTITION BY ...) with no frame is a neat trick: it attaches the group size to every row without a join.

Why you cannot filter a window function, and what to do instead

This is the single most common failure, and the error message is the useful part:

SELECT s.id, s.amount
FROM sales s
WHERE ROW_NUMBER() OVER (PARTITION BY s.rep_id ORDER BY s.amount DESC) = 1;

PostgreSQL rejects this with window functions are not allowed in WHERE. It is not a stylistic objection; there is no such thing to test yet at that point in the query. HAVING does not help either.

The fix is to compute the window first and filter its output, which means a subquery or a CTE:

WITH ranked AS (
  SELECT s.id, s.rep_id, s.region, s.amount, s.sold_at,
         ROW_NUMBER() OVER (
           PARTITION BY s.rep_id
           ORDER BY s.amount DESC, s.id
         ) AS rn
  FROM sales s
)
SELECT id, rep_id, region, amount, sold_at
FROM ranked
WHERE rn = 1;

Note the s.id tiebreaker in the window's ORDER BY. Without it, if two sales tie for a rep's largest amount, which one survives is not guaranteed, and the query can return a different row on a different run or after a plan change.

QUALIFY, and the portable equivalent

Snowflake, BigQuery and Databricks have QUALIFY, which filters on a window function directly and is genuinely nicer to read:

SELECT id, rep_id, region, amount, sold_at
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY rep_id ORDER BY amount DESC) = 1;

Standard SQL has no QUALIFY, and neither do PostgreSQL, MySQL, SQL Server or Oracle. So the portable pattern is the CTE above, and the interview answer is: name the filter the same way, use a CTE, and say why. Some engines also allow it inside a subquery in the FROM clause, which is the same idea with less typing:

SELECT *
FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (PARTITION BY s.rep_id ORDER BY s.amount DESC, s.id) AS rn
  FROM sales s
) ranked
WHERE ranked.rn <= 3;

Logical order, and why it matters

The evaluation order of a SELECT is: FROM and WHERE, then GROUP BY and HAVING, then window functions, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. Window functions sit after filtering and grouping, which explains three things that otherwise look arbitrary.

You cannot reference a window function in WHERE or HAVING, as above. You cannot use a SELECT-list alias inside a window's ORDER BY in the same query level, so a long window clause must be spelled out or hoisted into a WINDOW clause. And the frame operates over rows that have already been filtered and grouped, which means the filter you care about has to be applied earlier:

-- The filter runs first, so this really is a running total for EMEA only.
SELECT s.id, SUM(s.amount) OVER (ORDER BY s.sold_at) AS running
FROM sales s
WHERE s.region = 'EMEA';

A related trap: ROWS mode with no ORDER BY in the window specification is an error, because physical row order is undefined without one. If you want a whole-partition aggregate, write the frame you mean or omit ORDER BY entirely so you get the default full-partition frame.

Performance

A window function has to see the other rows in its partition, so the engine usually has to sort. PARTITION BY region ORDER BY sold_at needs a sort on those two columns, and that sort is usually the dominant cost. The index defined at the top of this post, on (region, sold_at), can supply exactly that ordering and let the planner use an index scan instead of a sort node, so checking the plan with EXPLAIN ANALYZE is worth doing before assuming the query is fine.

Three practical rules. First, reduce the grain before you window. Summing raw events to daily per-rep totals and then taking a running total is far cheaper than windowing every event, because the window has fewer rows to sort and fewer to aggregate.

WITH daily AS (
  SELECT s.rep_id,
         s.sold_at::date AS sold_on,
         SUM(s.amount) AS revenue
  FROM sales s
  GROUP BY s.rep_id, s.sold_at::date
)
SELECT rep_id, sold_on, revenue,
       SUM(revenue) OVER (PARTITION BY rep_id ORDER BY sold_on) AS running_revenue
FROM daily;

Second, filter inside the subquery that feeds the window, not outside it, so the sort operates on fewer rows. Third, prefer ROWS to RANGE where the semantics allow it: peer detection in RANGE requires comparing each row against its partition's sort keys, and with high-cardinality timestamps it buys you nothing. Very large explicit frames such as a rolling quarter across millions of rows still cost real time, because the engine needs either a sliding window structure or repeated work per row, so measure rather than assume.

Traps worth volunteering before the interviewer finds them

Nulls interact with the sort order. In PostgreSQL, ORDER BY amount DESC puts nulls first by default, so FIRST_VALUE(amount) over that ordering hands back null if any null exists, which is a confusing way to discover a data quality problem.

SELECT s.id, s.amount,
       FIRST_VALUE(s.amount) OVER (ORDER BY s.amount DESC) AS highest_with_nulls,
       FIRST_VALUE(s.amount) OVER (ORDER BY s.amount DESC NULLS LAST) AS highest_ignoring_nulls
FROM sales s;

The default differs between engines, so write NULLS FIRST or NULLS LAST explicitly wherever the answer depends on it.

Ordering must be deterministic. Any tie in the window's ORDER BY makes the output unspecified, which quietly breaks ROW_NUMBER de-duplication, running totals that depend on row order, and reproducibility between runs. Add a unique column as the final sort key.

And the frame operates on the post-GROUP BY grain. A "running total per day" written over raw sales is a running total over sales ordered by day, so each day contributes several rows and the values jump around. Aggregate to the grain you actually want first.

Related reading: the system design cheat sheet covers indexing and access patterns behind these queries, the Go post covers the service side of serving them, and the React post covers the client side.

Want the full set?

• 360 questions, every one with a model answer
• SQL + stats cheat sheet
• A/B testing framework
• Case-study walkthroughs