Window Function Basics Interview Questions & Answers

A window function performs a calculation across a set of rows — the window — that are related to the current row, without collapsing them into one row. Unlike a GROUP BY aggregate, every input row stays in the output, but each gets an extra computed value.

-- each employee row keeps its detail AND gets the dept average alongside it
SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM   employees;

The OVER clause is what makes a function a window function. Window functions are ideal for running totals, rankings, moving averages, and row comparisons.

Rule of thumb: a window function adds a per-row analytic value while keeping every row — think "aggregate without losing the detail."

The OVER clause defines the window — the set of rows a window function operates on for each row. It can contain three optional parts: PARTITION BY (split rows into groups), ORDER BY (order rows within the window), and a frame clause (ROWS/RANGE, narrowing the window further).

SELECT name, salary,
       SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS running_total
FROM   employees;

An empty OVER () makes the window the entire result set — every row sees all rows.

Rule of thumb: OVER turns an ordinary function into a window function and specifies which rows it looks at.

GROUP BY collapses rows — one output row per group. A window function preserves every row and attaches the aggregate alongside each one.

-- GROUP BY: one row per department
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- window: every employee row, plus their department's average
SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

This is why you can compare a row to its group (e.g. salary vs dept_avg) with a window function — impossible with a bare GROUP BY because the detail rows are gone.

Rule of thumb: GROUP BY summarizes into fewer rows; a window function annotates each row without reducing the count.

PARTITION BY divides the rows into independent groups (partitions); the window function restarts for each partition. It's the windowing analog of GROUP BY, but the rows aren't collapsed.

-- numbering restarts at 1 within each department
SELECT name, dept_id,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM   employees;

Without PARTITION BY, the whole result set is one single partition.

Rule of thumb: PARTITION BY says "compute this window function separately within each group."

Both split rows into groups, but the output shape differs:

• GROUP BY produces one row per group — it reduces the result.
• PARTITION BY keeps all rows, computing the window function within each group while leaving the detail intact.

-- 1 row per dept
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;

-- every row, with its dept's count attached
SELECT name, dept_id, COUNT(*) OVER (PARTITION BY dept_id) AS dept_count
FROM employees;

Rule of thumb: same grouping idea, different result — GROUP BY collapses, PARTITION BY annotates.

ORDER BY inside OVER orders the rows within each partition, which matters for two reasons:

1. Ranking functions (ROW_NUMBER, RANK, LAG, LEAD) need an order to be meaningful.
2. For aggregates, adding ORDER BY switches the default frame to a running (cumulative) calculation up to the current row.

-- without ORDER BY: same total for whole partition
SUM(amount) OVER (PARTITION BY user_id)
-- with ORDER BY: a running total up to each row
SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date)

It is independent of the query's outer ORDER BY, which only sorts the final output.

Rule of thumb: ORDER BY in OVER orders rows for the window calc (and triggers running aggregates); the outer ORDER BY sorts the result.

Use an aggregate (SUM) with OVER (... ORDER BY ...). The ORDER BY makes the default frame cumulative — rows from the start of the partition up to the current row.

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

Add PARTITION BY to reset the running total per group (e.g. per customer):

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)

Rule of thumb: SUM(x) OVER (ORDER BY ...) is the canonical running total; add PARTITION BY to restart it per group.

Yes — SUM, AVG, COUNT, MIN, and MAX all work as window functions simply by adding an OVER clause. They then compute over the window instead of collapsing rows.

SELECT name, salary, dept_id,
       COUNT(*)   OVER (PARTITION BY dept_id) AS dept_headcount,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM   employees;

The same function name behaves as a group aggregate (with GROUP BY) or a window aggregate (with OVER) depending on context.

Rule of thumb: any aggregate becomes a window function just by adding OVER — no GROUP BY required.

Window functions fall into three families:

• Aggregate windows — SUM, AVG, COUNT, MIN, MAX over a window.
• Ranking functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST — position a row within its partition.
• Value / offset functions — LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE — pull a value from another row in the window.

ROW_NUMBER() OVER (ORDER BY score DESC)        -- ranking
LAG(price)   OVER (ORDER BY day)               -- offset
AVG(price)   OVER (PARTITION BY product_id)    -- aggregate

Rule of thumb: aggregate windows summarize, ranking functions order, offset functions reach to neighboring rows.

Window functions are only allowed in the SELECT list and the ORDER BY clause. They are not allowed in WHERE, GROUP BY, or HAVING, because windows are evaluated after those clauses (after grouping and filtering).

-- ILLEGAL: window function in WHERE
SELECT name FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary) <= 5;   -- error

-- LEGAL: compute in a subquery/CTE, then filter
SELECT name FROM (
    SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 5;

Rule of thumb: you can't filter on a window function directly — wrap it in a CTE or subquery and filter the alias.

Window functions run late in logical query processing — after FROM, WHERE, GROUP BY, and HAVING, but before the final ORDER BY, DISTINCT, and LIMIT. That ordering explains two facts:

1. You can't reference a window function in WHERE/HAVING (they run earlier).
2. A window function operates on the rows that survived WHERE/GROUP BY.

-- the SUM window only sees rows passing the WHERE filter
SELECT name, SUM(salary) OVER ()
FROM   employees
WHERE  active = true;     -- filter applied BEFORE the window

Rule of thumb: filtering happens first, windows next, final sort/limit last — so windows see filtered rows but can't be filtered themselves.

The WINDOW clause lets you define a window once and reuse it by name across multiple functions, avoiding repetition. It sits after HAVING and before ORDER BY.

SELECT name, dept_id, salary,
       RANK()      OVER w AS rnk,
       AVG(salary) OVER w AS dept_avg
FROM   employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);

Both functions share window w. This is cleaner than repeating the same PARTITION BY ... ORDER BY ... in every function. (Supported in PostgreSQL, MySQL 8+, SQL Server has limited support.)

Rule of thumb: define a window once in the WINDOW clause when several functions share it.

Generally no — most databases do not support COUNT(DISTINCT ...) OVER (...). DISTINCT aggregation isn't allowed with the OVER clause in standard SQL, PostgreSQL, and SQL Server.

-- typically ERRORS
COUNT(DISTINCT customer_id) OVER (PARTITION BY region)

-- workaround: DENSE_RANK over the values, take the max
SELECT region,
       MAX(DENSE_RANK() OVER (PARTITION BY region ORDER BY customer_id))
           OVER (PARTITION BY region) AS distinct_customers
FROM orders;

Common workarounds are a DENSE_RANK trick, or pre-aggregating distinct values in a CTE.

Rule of thumb: COUNT(DISTINCT) as a window function usually isn't allowed — pre-aggregate or use a DENSE_RANK workaround.

Window functions require the engine to sort or hash rows by the PARTITION BY/ORDER BY keys, which is the main cost. Tips:

• Index the partition/order columns so the engine can avoid a separate sort.
• Each distinct window definition may add its own sort — share windows (named WINDOW clause) where possible.
• Filter rows before the window (WHERE) to shrink the input.
• Beware huge partitions and unbounded frames (RANGE with peers) — they scan more rows per output row.

Rule of thumb: window functions trade a sort for analytics — index the partition/order keys and minimize the number of distinct windows.

Before window functions, computing running totals, rankings, or row-to-row comparisons required correlated subqueries or self-joins, which are verbose and often O(n²). Window functions express the same logic in one pass, more readably and usually faster.

-- old self-join running total (slow, O(n^2))
SELECT a.day, SUM(b.amount) AS rt
FROM sales a JOIN sales b ON b.day <= a.day
GROUP BY a.day;

-- window function (one pass)
SELECT day, SUM(amount) OVER (ORDER BY day) AS rt FROM sales;

Rule of thumb: replace self-joins/correlated subqueries for running totals and rankings with window functions — clearer and faster.

Use the FILTER (WHERE ...) clause (PostgreSQL/SQLite) or a CASE expression inside the aggregate (portable) to aggregate only rows meeting a condition within the window.

-- PostgreSQL FILTER
SELECT region,
       COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY region) AS paid
FROM orders;

-- portable CASE equivalent
SELECT region,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)
           OVER (PARTITION BY region) AS paid
FROM orders;

Rule of thumb: use FILTER (WHERE ...) where supported, or SUM(CASE WHEN ...) for a portable conditional window aggregate.

An empty OVER () makes the window the entire result set — every row sees all the rows. It's used to attach a grand total or overall aggregate to each row without grouping.

SELECT name, salary,
       salary * 100.0 / SUM(salary) OVER () AS pct_of_total_payroll
FROM   employees;

Here SUM(salary) OVER () is the company-wide payroll, repeated on every row, so you can compute each person's share.

Rule of thumb: OVER () with no partition or order = the whole result set — perfect for "share of total" calculations.

Since window functions aren't allowed in WHERE, compute the window value in a CTE or subquery, then filter the alias in the outer query. This is the standard pattern for "top-N per group" and deduplication.

-- keep only the highest-paid employee per department
WITH ranked AS (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, dept_id, salary
FROM   ranked
WHERE  rn = 1;

Rule of thumb: wrap the window function in a CTE, then filter its output column — you can't put it in WHERE directly.