SQL window functions interview questions, with worked answers

SQL window function interview questions usually test ranking (ROW_NUMBER, RANK, DENSE_RANK), running totals with SUM OVER, comparisons with LAG and LEAD, and top N per group. Interviewers also check whether you understand PARTITION BY, ORDER BY inside OVER, frame clauses, and why window functions cannot go directly in a WHERE clause.

If you are interviewing for a data analyst, data engineer, BI or SQL developer role, window functions are close to guaranteed. They are the line between someone who can write SELECT statements and someone who can answer real business questions in one query. The good news is that the same eight or nine patterns cover most of what you will be asked.

This post works through each pattern with a small dataset, the query, the result, and the sentence you can say out loud to explain it. The syntax shown is standard SQL that works in PostgreSQL, SQL Server, MySQL 8 and later, Oracle and Snowflake, with small differences noted where they matter.

What is a window function, in one sentence?

A window function calculates a value for each row using a set of related rows, without collapsing those rows the way GROUP BY does. That is the answer interviewers want first. Then add the three parts of the OVER clause: PARTITION BY splits rows into groups, ORDER BY sets the order inside each group, and the frame decides which rows around the current row are included.

GROUP BY gives me one row per group. A window function keeps every row and adds a calculated column, like a rank or a running total, based on the rows in its partition.

We will use one simple table for most examples: employees with columns emp_name, dept, salary and hire_date, and a second table monthly_sales with columns region, month and revenue.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

This is the most common opening question. All three assign a position based on ORDER BY. The difference is how they handle ties. Say four engineers in one department earn 90, 85, 85 and 80 thousand.

• ROW_NUMBER always gives unique numbers. Ties are broken arbitrarily unless you add a tiebreaker column to ORDER BY.

• RANK gives ties the same number and then skips, so after two people at 2 the next is 4.

• DENSE_RANK gives ties the same number and does not skip, so the next is 3.

Say this follow-up before they ask: if you use ROW_NUMBER for deduplication or top N, always add a deterministic tiebreaker, such as ORDER BY salary DESC, emp_id, so results do not change between runs.

How do you find the Nth highest salary in each department?

This is the classic. Use DENSE_RANK if "second highest salary" means the second distinct value, which is how most interviewers mean it. Because you cannot filter on a window function in WHERE, wrap it in a CTE or subquery.

WITH ranked AS ( SELECT emp_name, dept, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees ) SELECT emp_name, dept, salary FROM ranked WHERE rnk = 2;

Then explain why: window functions are evaluated after WHERE, GROUP BY and HAVING, but before the final ORDER BY. So the rank does not exist yet when WHERE runs. Snowflake, BigQuery and Databricks support a QUALIFY clause that filters on window results directly, which is worth mentioning if the company uses one of them.

Clarifying questions that score points

• If two people share the second highest salary, should both be returned?

• If a department has only one employee, should it appear with no result, or be skipped?

• Should NULL salaries be excluded or treated as the lowest?

How do you calculate a running total?

Use SUM with OVER and an ORDER BY. Adding PARTITION BY restarts the total for each group.

SELECT region, month, revenue, SUM(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue FROM monthly_sales;

The interviewer may ask why you wrote ROWS BETWEEN explicitly. Here is the trap: when you use ORDER BY without a frame, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that share the same ORDER BY value are treated as peers and included together. If two rows have the same month, both get the combined total. ROWS counts physical rows, which is usually what you want for a running total.

How do LAG and LEAD work, and when would you use them?

LAG returns a value from a previous row in the partition, LEAD from a following row. They are the cleanest way to compare a row with its neighbour, for month over month growth, time between events or detecting changes in status.

SELECT region, month, revenue, LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY region ORDER BY month), 0), 1) AS mom_growth_pct FROM monthly_sales;

Point out three details. The first row in each partition has no previous value, so LAG returns NULL unless you pass a default, as in LAG(revenue, 1, 0). NULLIF protects against dividing by zero. And multiplying by 100.0 avoids integer division in databases like SQL Server and PostgreSQL. In a real answer you can also use a named WINDOW clause to avoid repeating the same OVER three times, where your database supports it.

How do you get the top N rows per group?

Same shape as the Nth salary question, but with a range instead of an exact match. Pick the ranking function based on how ties should be treated.

WITH ranked AS ( SELECT region, product, units, ROW_NUMBER() OVER (PARTITION BY region ORDER BY units DESC, product) AS rn FROM product_sales ) SELECT region, product, units FROM ranked WHERE rn <= 3;

Explain your choice: ROW_NUMBER guarantees exactly three rows per region. RANK or DENSE_RANK could return more if there are ties at third place. Ask the interviewer which behaviour the business wants. That one question shows you think about outcomes, not just syntax.

What other window function questions should you prepare?

Once you are comfortable with the core patterns, these come up in second technical rounds, especially for data engineer and senior analyst roles.

1. Remove duplicates keeping the latest record. Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and keep rn = 1. In a DELETE, many databases need a CTE or a join back to the table.

2. Percent of total. revenue * 100.0 / SUM(revenue) OVER (PARTITION BY region) gives each row's share without a separate GROUP BY query.

3. Moving average. AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) gives a three month moving average. Mention that the first two rows average fewer months.

4. LAST_VALUE surprise. LAST_VALUE with only ORDER BY often returns the current row, because the default frame ends at the current row. Fix it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

5. NTILE for buckets. NTILE(4) OVER (ORDER BY spend DESC) splits customers into quartiles. Note that bucket sizes can differ by one when rows do not divide evenly.

6. Gaps and islands. To find consecutive login days, subtract ROW_NUMBER from the date. Rows in the same streak share the same result, so you can GROUP BY it and count.

How should you explain your query in the interview?

Writing correct SQL is half the score. The other half is narration. Interviewers want to hear how you think, especially in live coding on a shared editor. Use a simple four step rhythm.

1. Restate the question and confirm edge cases: ties, NULLs, empty groups.

2. Describe the shape before writing: partition by what, order by what, which function.

3. Write the query in small pieces, starting with the CTE and checking it mentally.

4. Walk through two or three sample rows to show the output is right.

I will partition by department because the ranking restarts for each one, order by salary descending, and use DENSE_RANK since you said tied salaries count as one position. Then I filter in an outer query, because the rank is not available in WHERE. In a project at my last company, I used the same pattern to pick the top three products per store for a weekly report.

Practise this out loud, not just on paper. A few timed mock interviews for a data role will show you quickly whether your explanations are clear or rambling. For broader preparation, see our SQL developer interview questions, the data analyst question bank and our technical interview tips.

If you are already in a role and stuck on a gnarly query at work, SQL job support pairs AI with expert help for real tickets. And if you want to rehearse these exact questions with feedback, you can try InterviewPlan AI free with 5 live minutes and 100 AI credits, no card required.

Frequently asked questions

Can I use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE, GROUP BY and HAVING, so their results do not exist yet when WHERE runs. Put the window function in a CTE or subquery and filter in the outer query. Snowflake, BigQuery and Databricks also support QUALIFY, which filters on window results directly.

When should I use DENSE_RANK instead of RANK?

Use DENSE_RANK when you want positions without gaps after ties, such as finding the second highest distinct salary. Use RANK when gaps are meaningful, like a leaderboard where two people tied for second means nobody is third. Always confirm with the interviewer how ties should be treated.

What is the default window frame in SQL?

When OVER includes ORDER BY but no frame, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without ORDER BY, the frame is the whole partition. The RANGE default treats tied rows as peers, which can surprise you in running totals and LAST_VALUE.

Do window functions work in MySQL?

Yes, from MySQL 8.0 onward. Earlier versions did not support them, and people used session variables to simulate ranking. If an interviewer mentions an older MySQL version, say you would check the version first and use a self join or correlated subquery as a fallback.

How many window function questions are asked in a data analyst interview?

It varies by company, but expect at least one in most technical SQL rounds for data roles, often as the hardest question. Ranking, running totals, LAG and top N per group cover the majority. Practise explaining each one aloud as well as writing it.