SQL window functions interview questions

"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.

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.

-- 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

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.

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:

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:

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.

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 guide covers the wider round, and 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.