--- title: SQL Window Functions Interview Questions (2026) description: SQL window functions interview questions with worked queries — ROW_NUMBER vs RANK, running totals, LAG, frames, and the top-N-per-group question everyone gets. url: https://usegreenroom.app/blog/sql-window-functions-interview-questions last_updated: 2026-08-01 --- ← Back to blog Technical # SQL window functions interview questions August 1, 2026 · 12 min read ![SQL window functions interview questions and answers — guide cover from Greenroom, the AI mock interviewer](/assets/blog/sql-window-functions-interview-questions-hero.webp) "Find the second highest salary in each department." He wrote a correlated subquery, then a self-join, then began describing a solution involving a temporary table, and about ninety seconds in the interviewer said, gently, "have you used window functions?" He had heard of them. In the way you have heard of a country you have not visited. Window functions are the single highest-leverage topic in SQL interviews, because one pattern — partition, rank, filter — answers a whole class of questions that otherwise require awkward self-joins. This guide covers the **SQL window functions interview questions** that recur, with worked queries you can say out loud. ## What a window function actually is The definition that scores: a window function computes a value across a set of rows related to the current row, **without collapsing them**. A `GROUP BY` reduces many rows to one; a window function keeps every row and adds a column. That one sentence answers the most common opening question, and it also explains why you cannot use a window function in a `WHERE` clause — the window is computed after `WHERE`, which is why filtering on a rank always requires a subquery or CTE. ![Diagram of the window function topics asked in SQL interviews — ranking functions, aggregate windows, offset functions, frame clauses, and the top-N-per-group pattern](/assets/blog/sql-window-functions-interview-questions-diagram.webp) Almost every window function question in an interview is one of these five shapes. Top-N-per-group is the one you are most likely to be asked verbatim. ## Ranking: ROW_NUMBER, RANK, DENSE_RANK The tie behaviour is the whole question: - **`ROW_NUMBER`** — 1, 2, 3, 4. Always unique, ties broken arbitrarily unless you add a tiebreaker. - **`RANK`** — 1, 2, 2, 4. Ties share a rank and the next value skips. - **`DENSE_RANK`** — 1, 2, 2, 3. Ties share a rank and nothing is skipped. The follow-up: which do you use for "the second highest salary"? If duplicates should count once, `DENSE_RANK`. If you want the second row regardless of ties, `ROW_NUMBER`. Saying "it depends on how ties should be treated — which do you want?" is a better answer than picking one silently. ## The top-N-per-group pattern This is the most-asked window question in any SQL interview, in some phrasing. ```code:sql -- top 3 highest-paid employees in each department WITH ranked AS ( SELECT employee_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees ) SELECT employee_id, department_id, salary FROM ranked WHERE rnk <= 3 ORDER BY department_id, salary DESC; ``` Two things to say out loud while writing it. First, the filter has to live in an outer query because window functions are evaluated after `WHERE`. Second, `DENSE_RANK` means a department with three people tied at the top returns all three — and whether that is correct depends on the requirement, which you should ask about. ## Running totals and moving averages ```code:sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7 FROM orders; ``` The follow-up here separates people who memorised the syntax from people who understand it: **what is the default frame?** When you supply an `ORDER BY` without a frame clause, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. With `RANGE`, all rows with the same `ORDER BY` value are treated as peers and included together — so if two orders share a date, both are included in each other's running total. With `ROWS`, they are not. Being able to explain that difference is genuinely a senior-level SQL signal. ## LAG and LEAD For comparing a row to its neighbours: month-over-month growth, gap detection, session boundaries. ```code:sql SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS pct_change FROM monthly_revenue; ``` Note the `NULLIF` guarding against division by zero — interviewers notice defensive SQL. Also know the third argument: `LAG(revenue, 1, 0)` supplies a default instead of `NULL` for the first row. A classic follow-up: find consecutive days where a user was active. The answer is the **gaps-and-islands** pattern — subtract `ROW_NUMBER()` from the date; consecutive dates produce a constant difference, so you can group by it. Being able to name that pattern is worth real credit. ## FIRST_VALUE, LAST_VALUE, and the trap `FIRST_VALUE` works as expected. `LAST_VALUE` usually does not, because of the default frame: it looks only as far as the current row, so it returns the current row's value. The fix is explicit: ```code:sql LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ``` This is a favourite interview trap. Volunteering it unprompted lands very well. ## NTILE, and percentage of total `NTILE(4)` splits rows into four roughly equal buckets — used for quartiles and cohort analysis. Note that with an uneven row count the earlier buckets get the extra rows. Percentage of total is an aggregate window without an `ORDER BY`: ```code:sql SELECT category, revenue, ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total, ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 2) AS pct_of_region FROM sales; ``` The empty `OVER ()` — the whole result set as one window — surprises people, and it is a clean, common answer. ## Performance questions - **Window functions require a sort** per partition unless an index already provides the order. On large tables that sort is the cost. - **Reuse the window.** Naming it with a `WINDOW` clause avoids repeating the definition and lets the planner compute it once: `WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)`. - **Window versus self-join.** The window version is almost always faster and always more readable, because a self-join re-scans the table. - **Filter early.** Reduce rows in a CTE before the window if the requirement allows it. **The core truth:** almost every window question is the same shape wearing different clothes — partition, order, compute, then filter in an outer query. Learn the shape and the frame semantics, not a list of function names. ## Questions you should expect verbatim - "What is the difference between `RANK` and `DENSE_RANK`?" - "Find the second highest salary in each department." - "Calculate a running total of sales by date." - "Find month-over-month revenue growth." - "Find the top 3 products per category." - "Why can't you use a window function in a `WHERE` clause?" - "What is the difference between `ROWS` and `RANGE`?" Our [SQL interview questions](/blog/sql-interview-questions) guide covers the wider round, and [DBMS interview questions](/blog/dbms-interview-questions) covers the theory beneath it. ## Where each prep option actually helps - **The PostgreSQL documentation on window functions** — short, precise, and the frame-clause section answers the questions people fail. - **StrataScratch, DataLemur and LeetCode's database section** — the right place to build the muscle memory for top-N-per-group. - **Mode's SQL tutorial** — good for the analytical framing that these questions come wrapped in. - **ChatGPT** — will write the query. It will not ask you what happens when two rows share a date, which is the follow-up that separates candidates. - **Greenroom** — the spoken layer. [Ari, the AI interviewer](/) runs data rounds out loud, so you practise narrating a query rather than silently typing one. Honest tradeoff: Ari does not run your SQL, so pair it with a practice platform. ## Frequently asked questions ### What is a window function in SQL? A window function computes a value across a set of rows related to the current row without collapsing them. Where GROUP BY reduces many rows to one, a window function keeps every row and adds a column. That distinction also explains why a window function cannot appear in a WHERE clause — the window is evaluated after WHERE, so filtering on a rank always requires a subquery or CTE. ### What is the difference between ROW_NUMBER, RANK and DENSE_RANK? ROW_NUMBER always assigns unique consecutive numbers, breaking ties arbitrarily unless you add a tiebreaker, producing 1, 2, 3, 4. RANK gives tied rows the same value and then skips, producing 1, 2, 2, 4. DENSE_RANK gives tied rows the same value and skips nothing, producing 1, 2, 2, 3. Which one is correct for a question like second highest salary depends on how ties should be treated, and asking that is a better answer than choosing silently. ### How do you find the top N rows per group in SQL? Use a ranking window function partitioned by the group, then filter in an outer query or CTE. For example, select DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as a ranked column inside a CTE, then select from that CTE where the rank is less than or equal to three. The filter must be outside because window functions are evaluated after WHERE. ### What is the difference between ROWS and RANGE in a window frame? ROWS counts physical rows, so a frame of six preceding plus the current row is always seven rows. RANGE works on values, treating all rows with the same ORDER BY value as peers and including them together. This matters because the default frame when you supply an ORDER BY without a frame clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so ties in the ordering column are all included in each other's running total. ### Why does LAST_VALUE not return the last value in SQL? Because of the default frame. With an ORDER BY and no explicit frame, the window extends only up to the current row, so LAST_VALUE returns the current row's value rather than the final one in the partition. The fix is to specify the frame explicitly as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, and volunteering this trap before being asked is a strong signal in an interview. ### Are window functions faster than self-joins? Usually yes, and they are always more readable. A self-join re-scans the table to compare rows, while a window function makes a single pass with a sort per partition. The sort is the main cost, and it can be avoided when an index already provides the required order. Defining the window once with a named WINDOW clause also lets the planner compute it a single time rather than repeating the definition. SQL rounds are spoken as much as typed — the query is half the answer, the reasoning is the other half. [Greenroom](https://usegreenroom.app/) runs mock data interviews with Ari, who asks what happens when two rows share a date. Free to start. Curious how it works? See [how AI mock interviews work](/blog/ai-mock-interview).