Window functions interview questions, visualised

In this article

1. Window function basics
2. Ranking questions
3. Order of execution
4. Aggregates as windows, and frames
5. Offsets, buckets and reusable windows
6. Portability
7. Follow-up questions
8. Sources

Window function basics

This page belongs to the SQL interview questions guide. The examples use the ten-row empsalary table from the PostgreSQL tutorial: five rows in develop, two in personnel and three in sales.¹

What is a window function in SQL, and how does it differ from GROUP BY?

A window function calculates across a set of rows related to the current row, but unlike a GROUP BY aggregate it does not group them into a single output row, so every input row stays (PostgreSQL tutorial, August 2026). avg(salary) OVER (PARTITION BY depname) puts each department's average beside every employee: 5020.00 for develop, 3700.00 for personnel and 4866.67 for sales.¹ A GROUP BY would return three rows instead of ten.¹

Why it is asked: it separates people who know the keyword from people who know that rows are kept. Read the full explanation: PostgreSQL window function tutorial.

What does the OVER clause do?

The OVER clause marks a call as a window function and says which rows it sees. A window function call has OVER directly after the function name and arguments (an aggregate's optional FILTER clause can sit between them). The same avg(salary) is a plain aggregate returning one row for the whole set without OVER, and a window function with it (PostgreSQL function reference, August 2026). The ranking functions require it.

Why it is asked: it tests whether you can read the syntax that makes a query a window query. Read the full explanation: PostgreSQL window function reference.

What do PARTITION BY and ORDER BY do inside OVER?

A partition is a group of rows that share the PARTITION BY value, and the function runs separately in each one; leaving PARTITION BY out makes the whole result one partition. The ORDER BY inside OVER orders rows within each partition for the calculation (PostgreSQL tutorial, August 2026). row_number() OVER (PARTITION BY depname ORDER BY salary DESC) numbers the develop salaries 6000, 5200, 5200, 4500 and 4200 as 1 to 5.¹

Why it is asked: partition and order are the two choices you make in almost every window query. Read the full explanation: PostgreSQL window function tutorial.

Ranking questions

What is the difference between row_number, rank and dense_rank?

The row_number function gives every row a unique number, and tied rows are numbered in an unspecified order, so a tiebreaker column is needed for repeatable results (PostgreSQL tutorial, August 2026). The rank function gives tied rows the same number and then leaves gaps; dense_rank gives tied rows the same number with no gaps (PostgreSQL function reference, August 2026). On illustrative values 1,1,2,3,3,3,4,4, rank gives 1,1,3,4,4,4,7,7 and dense_rank gives 1,1,2,3,3,3,4,4.¹

On those values row_number gives each row a unique number from 1 to 8.¹

Text description of the diagram

• On values 1,1,2,3,3,3,4,4, rank gives 1,1,3,4,4,4,7,7 and dense_rank gives 1,1,2,3,3,3,4,4.
• On those values row_number gives each row a unique number from 1 to 8.

Why it is asked: the three differ only when ties exist, and interviewers want to hear that. Read the full explanation: PostgreSQL window function reference.

How do you find the second highest salary per department when salaries tie?

Use dense_rank and keep rank 2, because it counts distinct salaries, so ties do not hide the second value. For illustrative salaries 95, 90, 90 and 85, rank gives 1,2,2,4, dense_rank gives 1,2,2,3 and row_number gives 1,2,3,4.¹ Filtering dense_rank = 2 and rank = 2 both return the two 90 rows, while row_number = 2 returns one of them, chosen arbitrarily. With a tied top, rank would skip 2 entirely.

Why it is asked: it checks whether you pick the ranking function by the tie behaviour you need. Read the full explanation: the PostgreSQL ranking function reference.

SELECT depname, salary
FROM (
  SELECT depname, salary,
         dense_rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos
  FROM empsalary
) ranked
WHERE pos = 2;

How do you get the top N rows per group?

Compute a number in a subquery, then filter on it in the outer query, because a window function cannot appear in WHERE. The PostgreSQL tutorial does this with row_number() ... AS pos and WHERE pos < 3 (PostgreSQL tutorial, August 2026). On the sample table that returns six rows: 6000 and 5200 for develop, 3900 and 3500 for personnel, 5000 and 4800 for sales.¹ Use rank to keep ties, and add a tiebreaker column for repeatable results.

Why it is asked: top-N-per-group is the standard use of ranking, and it exposes whether you know the filter must be outside. Read the full explanation: PostgreSQL window function tutorial.

How do you remove duplicate rows with a window function?

Number the rows with row_number(), partitioned by the columns that define a duplicate and ordered by the column that picks the survivor, then keep number 1 in an outer query. For illustrative keys a, a, b the numbers are 1, 2, 1, so one a row and the b row stay.¹ Because a window function cannot sit in WHERE, wrap it in a subquery or a common table expression (CTE). A common table expression is a named subquery introduced with WITH.

Why it is asked: deduplication is a routine data task, and it tests whether you can combine ranking with an outer filter. Read the full explanation: PostgreSQL window function tutorial.

Order of execution

Why can't you use a window function in WHERE or HAVING?

Window functions are allowed only in the SELECT list and the ORDER BY clause, and they are forbidden in GROUP BY, HAVING and WHERE. Window functions logically run after the WHERE, GROUP BY and HAVING clauses, so their result does not exist yet when those clauses run. To filter or group on a window result, use a sub-select (PostgreSQL tutorial, August 2026).

1 · Filter2 · Group3 · Window4 · OutputReplay

Step-by-step description

1. The where clause removes rows first.
2. Group by and having run next.
3. Window functions see the remaining rows.
4. The select list and order by come last.

• Window functions logically run after the where, group by and having clauses.
• Window functions are allowed only in the select list and the order by clause.

Why it is asked: it tests your model of the logical order of a query. Read the full explanation: PostgreSQL window function tutorial.

Why does adding a WHERE change my window results?

A row removed by WHERE is not seen by any window function, so a running total or a rank is computed only over the rows that remain. To keep the full history and still display only some rows, calculate the window in a subquery and filter in the outer query (PostgreSQL tutorial, August 2026).

Why it is asked: a filter in the wrong place gives plausible but wrong numbers, a common bug in reports. Read the full explanation: PostgreSQL window function tutorial.

Does ORDER BY inside OVER sort the output?

No. The ORDER BY inside OVER orders rows only for the window calculation, and the window ordering need not match the order of the output (PostgreSQL tutorial, August 2026). When the display order matters, add a query-level ORDER BY; the page on how to sort rows with SQL order by covers ascending and descending order.

Why it is asked: it catches candidates who treat the window order as a sort of the result. Read the full explanation: SQL order by.

How do you show each group's share of the total in one query?

Put a window over an aggregate: sum(salary) / sum(sum(salary)) OVER () divides each department's total by the grand total, because sum(sum(salary)) OVER () adds the grouped totals across all rows. The query runs GROUP BY first, so the window sees one row per department, not the original rows (PostgreSQL tutorial, August 2026). On the sample table the department totals are 25100, 7400 and 14600 over a total of 47100, giving shares of 0.53, 0.16 and 0.31.¹

SELECT depname, sum(salary) / sum(sum(salary)) OVER () AS share
FROM empsalary
GROUP BY depname;

Why it is asked: it shows you know grouping happens before the window. Read the full explanation: PostgreSQL window function tutorial.

Aggregates as windows, and frames

What is a running total, and why do tied rows share one?

An aggregate with ORDER BY inside OVER gives a running total, because the default frame runs from the partition start through the last peer of the current row (PostgreSQL function reference, August 2026). Peer rows are rows that tie on the ORDER BY value, so they share one total: in sum(salary) OVER (ORDER BY salary) both 4800 rows show 25700 and both 5200 rows show 41100.¹ A ROWS frame counts physical rows and separates them.

Why it is asked: it shows whether you know the default frame includes peers. Read the full explanation: PostgreSQL window function reference.

What is the default window frame?

A window frame is the subset of the partition that a frame-based function sees for the current row (PostgreSQL expressions, August 2026). With ORDER BY the default runs from the partition start through the last peer of the current row. Without ORDER BY all rows are peers, so the frame is the whole partition (PostgreSQL function reference).

Why it is asked: most surprising window results come from a default the writer did not know. Read the full explanation: PostgreSQL window function calls.

What is the difference between ROWS, RANGE and GROUPS frames?

The ROWS unit counts the exact number of rows in the frame, RANGE looks for a value offset so peers are included, and GROUPS counts groups of tied rows; all three are frame units in the PostgreSQL syntax (PostgreSQL expressions, August 2026). On illustrative values 1,1,3,5,5,5,6 with a frame of 1 preceding to 1 following, the frame sums are 2,5,9,13,15,16,11 for ROWS, 2,2,3,21,21,21,21 for RANGE and 5,5,20,24,24,24,21 for GROUPS.¹

Text description of the diagram

• On values 1,1,3,5,5,5,6 with a frame of 1 preceding to 1 following, the frame sums are 2,5,9,13,15,16,11 for rows, 2,2,3,21,21,21,21 for range and 5,5,20,24,24,24,21 for groups.
• Groups counts groups of tied rows.

Why it is asked: it separates memorised syntax from understanding what a frame counts. Read the full explanation: PostgreSQL window function calls.

How do you calculate a moving average?

Use an aggregate with a ROWS frame: avg(x) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages the current row and the two before it. For illustrative values 10, 20, 60, 40 and 50 it gives 10, 15, 30, 40 and 50, and the first two rows average fewer rows because nothing earlier exists.¹ Choose ROWS rather than the default RANGE: a RANGE 2 PRECEDING frame is a value offset (two days), not two rows, and tied dates change what it holds.

Why it is asked: it applies frames to a real analytics task. Read the full explanation: PostgreSQL window function calls.

Why does last_value return the current row?

The functions first_value, last_value and nth_value look only at the window frame, and the default frame ends at the last peer of the current row, so last_value returns the current row's value. The PostgreSQL manual says the default frame is likely to give unhelpful results here (PostgreSQL function reference, August 2026). Extend it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. For illustrative values 10, 20, 30 the default gives 10, 20, 30 and the full frame gives 30, 30, 30.¹

Why it is asked: it is the classic frame trap. Read the full explanation: PostgreSQL window function reference.

Offsets, buckets and reusable windows

How do lag and lead work?

The function lag(value, offset, default) returns the value from offset rows before the current row in the partition, and lead looks the same distance forward. If omitted, the offset defaults to 1 and the default value to NULL, so the first row's lag is NULL (PostgreSQL function reference, August 2026). For illustrative values 10, 20, 60, 40 and 50, value - lag(value) gives NULL, 10, 40, -20 and 10.¹

Why it is asked: row-to-row change is a frequent analytics request. Read the full explanation: PostgreSQL window function reference.

How do you find runs of consecutive days (gaps and islands)?

Subtract each row's row_number from its day number: consecutive days give the same difference, so grouping by it returns one row per run. Another route is lag plus a running sum of the breaks. For illustrative day numbers 1, 2, 3, 5, 6 and 8 the differences are 0, 0, 0, 1, 1 and 2, giving runs of 3, 2 and 1 days.¹ Remove duplicate days first, or use dense_rank instead of row_number so repeated days do not break the pattern.

Why it is asked: it combines ranking, offsets and grouping in one problem. Read the full explanation: SQL interview questions guide.

What do percent_rank and ntile do?

The percent_rank function is (rank - 1) / (total partition rows - 1), so it ranges from 0 to 1; the first row is always 0. The last row reaches 1 only when it is not tied with another row. The ntile(num_buckets) function divides the partition as equally as possible (PostgreSQL function reference, August 2026). On the five develop salaries 6000, 5200, 5200, 4500 and 4200, percent_rank gives 0, 0.25, 0.25, 0.75 and 1, and ntile(2) makes groups of three and two rows.¹

Why it is asked: it checks that you know the relative-position functions exist. Read the full explanation: PostgreSQL window function reference.

How do you reuse one window definition?

A WINDOW clause names a window specification once, and each call refers to it with OVER w (PostgreSQL expressions, August 2026). Several functions can then share one definition, for example WINDOW w AS (PARTITION BY depname ORDER BY salary DESC) used by both rank() OVER w and row_number() OVER w. The query is shorter and the calls cannot drift apart.

Why it is asked: it shows fluency beyond the basic syntax. Read the full explanation: PostgreSQL window function calls.

Portability

Do window functions work the same in MySQL, SQL Server and Oracle?

The core ranking and offset functions are widely shared, but details differ between engines and versions, such as null handling in offset functions and the frame options available. Check the manual of the engine and version you run; this page does not cover SQL Server or Oracle syntax.

Why it is asked: it checks that you know where the dialects diverge. Read the full explanation: SQL Server interview questions and Oracle SQL interview questions.

Follow-up questions

Which window function questions should I practise first?

Practise the questions that combine ideas: ranking with ties, top N per group, a running total with its default frame, and the rule that a window result can only be filtered in an outer query. Then work through the wider SQL interview questions guide.

When should I use a window function instead of GROUP BY?

Use a window function when each input row must stay in the output beside a value computed over its group, such as an employee next to the department average. Use GROUP BY when one row per group is the answer.

How do you explain a window function query out loud?

Say the four choices in order: which rows form a partition, how they are ordered, which frame the function sees, and which function runs on that frame. Then say what happens to ties.

Can a window function use a conditional expression?

Yes: a CASE expression can sit inside the arguments, as in a conditional total. See case statements in SQL and conditional totals with sum and case when.

Sources

1. 3.5. Window Functions (PostgreSQL 18 documentation, tutorial) — PostgreSQL Global Development Group, 13 Aug 2026
2. 9.22. Window Functions (PostgreSQL 18 documentation, function reference) — PostgreSQL Global Development Group, 13 Aug 2026
3. 4.2. Value Expressions, window function calls (PostgreSQL 18 documentation) — PostgreSQL Global Development Group, 13 Aug 2026