SQL Window Functions Interview Questions and Answers

Window functions are the single highest-signal SQL topic in modern analyst, data-engineering and backend interviews. They separate people who can only GROUP BY from people who can compute rankings, running totals and row-to-row comparisons — the calculations real reporting demands. If you learn one advanced SQL topic before an interview, make it this one.

The examples use sales(id, region, sale_date, amount) and standard SQL. Window functions are supported in PostgreSQL, MySQL 8+, SQL Server 2012+ and Oracle.

What is a window function?

A window function computes a value across a set of rows related to the current row — the "window" — while keeping every input row in the output. It is written with an OVER (...) clause that defines partitioning, ordering and framing.

The defining contrast with GROUP BY: aggregation collapses rows; a window function adds a column and keeps them all. Say that first — it reframes every later question correctly.

SELECT region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;   -- every row kept, plus its region's total

Q1. Window function vs GROUP BY — explain the difference.

GROUP BY produces one output row per group; a window function produces one output row per input row, with the group calculation attached. If you need detail rows and a group value together, only the window function can do it in one pass.

-- GROUP BY: one row per region, detail lost
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Window: every sale, with its region total alongside
SELECT id, region, amount, SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

The killer example is "show each sale and what percent of its region it represents" — impossible with plain GROUP BY, trivial with amount / SUM(amount) OVER (PARTITION BY region). Reaching for that example demonstrates real fluency.

Interview note: Follow-up: "can you use both in one query?" Yes — a window function can operate over the results of a GROUP BY, since windowing is applied after grouping in the logical order.

Q2. ROW_NUMBER vs RANK vs DENSE_RANK — how do they differ on ties?

ROW_NUMBER always gives distinct numbers, breaking ties arbitrarily. RANK gives ties the same number then skips (1,1,3). DENSE_RANK gives ties the same number with no gap (1,1,2).

SELECT name, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM sales;

Pick by intent: "give me exactly the top 3 rows" wants ROW_NUMBER; "who tied for first" wants RANK or DENSE_RANK; "distinct ranking positions with no gaps" wants DENSE_RANK. Naming the tie behaviour precisely is the whole point of the question.

Interview note: Trap: "which one can produce more than N rows for top-N?" RANK and DENSE_RANK — because ties share a rank, WHERE rnk <= 3 can return four rows. ROW_NUMBER guarantees exactly N.

Q3. How do you get the top N per group?

Assign a per-group number with ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC) inside a subquery or CTE, then filter the outer query to <= N.

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

The must-explain detail: you cannot put WHERE rn <= 3 in the inner query, because window functions are evaluated after WHERE in the logical processing order. The subquery/CTE wrapper is what gives you a place to filter. That explanation is exactly what interviewers listen for.

Interview note: Follow-up: "what if you want ties included at rank N?" Switch ROW_NUMBER to RANK or DENSE_RANK so tied rows at the boundary all appear.

Q4. How do you compute a running total?

Add ORDER BY inside the OVER clause; with an ordered window, an aggregate accumulates from the frame start to the current row. SUM(amount) OVER (ORDER BY sale_date) gives a running total.

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

The explicit frame matters: with ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps together rows with equal sale_date. Spelling out ROWS avoids that surprise. Showing you know the default frame is a senior-level detail.

Interview note: Trap: "why did my running total jump for same-date rows?" Because the default RANGE frame includes all peer rows with the equal ORDER BY value. Use ROWS for a strict row-by-row accumulation.

Q5. What do LAG and LEAD do?

LAG returns a value from a previous row, LEAD from a following row, within the partition and ordering — enabling row-to-row comparisons like period-over-period change without a self-join.

SELECT sale_date, amount,
       LAG(amount)  OVER (ORDER BY sale_date) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS day_over_day
FROM sales;

Both take an optional offset and a default for the boundary rows (the first row has no previous). Before window functions, this required a correlated self-join; being able to say "LAG replaces the self-join" shows you understand what the feature is for.

Interview note: Follow-up: "what does LAG return for the first row?" NULL by default; pass a third argument as the default value, e.g. LAG(amount, 1, 0).

Q6. What is a window frame and when do you tune it?

A frame limits which rows within the partition the function considers for each current row. You tune it for moving windows — a 3-row moving average, a trailing-7-day sum — by specifying ROWS or RANGE bounds.

-- 3-row moving average
SELECT sale_date, amount,
       AVG(amount) OVER (ORDER BY sale_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM sales;

Frames apply to aggregate window functions (SUM, AVG, COUNT, MIN, MAX); ranking functions ignore frames. Knowing ROWS (physical row count) versus RANGE (value-based) is the distinction that comes up in follow-ups.

Interview note: Trap: "ROWS vs RANGE for a moving average?" ROWS counts exactly N physical rows; RANGE groups by value and can include more rows on ties. For a fixed N-row window, use ROWS.

Q7. Can you filter or aggregate on a window function's result directly?

Not in the same query level. Window functions are computed after WHERE, GROUP BY and HAVING, so you cannot reference the alias in those clauses — you must wrap the query in a subquery or CTE and filter outside.

-- Wrong: rn is not available in WHERE here
-- SELECT *, ROW_NUMBER() OVER (...) AS rn FROM sales WHERE rn = 1;   -- error

-- Right: filter in an outer query
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

This is a direct consequence of SQL's logical processing order, and interviewers love it because it tests whether you understand when windowing happens, not just how to write it.

Interview note: Follow-up: "where does windowing sit in the logical order?" After WHERE/GROUP BY/HAVING and before ORDER BY/DISTINCT. That ordering explains the wrapping requirement.

Q8. What is the difference between PARTITION BY and GROUP BY?

PARTITION BY divides rows into groups for the window function only, without collapsing them — every row stays. GROUP BY collapses each group to one row. PARTITION BY is "grouping without aggregation-collapse".

-- Rank employees within each region, keeping all rows
SELECT region, name, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;

An empty/omitted PARTITION BY treats the whole result as one window. The mental model to offer: PARTITION BY resets the window at each group boundary but never removes rows — which is precisely why window functions and GROUP BY solve different problems.

Interview note: Trap: "can you PARTITION BY one column and ORDER BY another?" Yes — partitioning defines the groups, ordering defines sequence within each group; they are independent knobs.

How to prepare

Load a table with a date column and a few groups, then write, in order: a running total, a 7-row moving average, a LAG-based day-over-day change, and a top-3-per-group query. Those four cover the entire window-function interview surface, and writing the top-N query forces you to internalize why the window function must live in a subquery. Deliberately trigger the default-RANGE running-total surprise once so the frame rules stick.

Pair this with the aggregate functions questions, since window aggregates build directly on plain aggregates, and the subqueries set for the wrapping pattern the top-N query relies on. The SQL learning path covers the logical query order that explains why filtering on a window alias fails. A focused mock interview on analytical SQL is the best way to make these patterns automatic under pressure.