SQL window function interview questions
Window functions are where many interviews get hard. Eight questions with the pattern behind each, run on a small sample database so you can check every number.
A window function computes a value over a set of related rows and keeps every row. The shape is function(...) OVER (PARTITION BY group ORDER BY sort frame). Windows run after WHERE, GROUP BY and HAVING, so you cannot filter on them in WHERE; wrap the query in a CTE or subquery first.
The sample database
All examples use three small tables with made-up data. Note the traps built into them: one employee has no department, two employees have the same salary, one department has no employees, and one sale has no amount.
departments(id, name, budget)
employees(id, full_name, department_id, manager_id, salary, hired_on)
sales(id, sold_on, region, amount)departments
| id | name | budget |
|---|---|---|
| 1 | Sales | 30000 |
| 2 | Engineering | 80000 |
| 3 | Support | 20000 |
| 4 | Finance | 15000 |
employees
| id | full_name | department_id | manager_id | salary | hired_on |
|---|---|---|---|---|---|
| 1 | Mara Lind | 2 | NULL | 9200 | 2019-03-04 |
| 2 | Joel Park | 2 | 1 | 7600 | 2020-06-15 |
| 3 | Ines Cole | 2 | 1 | 7600 | 2021-01-11 |
| 4 | Tomas Reyes | 1 | 5 | 6900 | 2018-09-01 |
| 5 | Nadia Voss | 1 | NULL | 6400 | 2017-05-22 |
| 6 | Owen Hart | 1 | 5 | 4300 | 2022-02-14 |
| 7 | Pia Novak | 3 | 8 | 3900 | 2023-04-03 |
| 8 | Sven Ito | 3 | NULL | 5200 | 2020-11-30 |
| 9 | Lea Brandt | NULL | NULL | 4800 | 2024-01-08 |
| 10 | Ravi Shah | 2 | 1 | 6100 | 2022-08-19 |
sales
| id | sold_on | region | amount |
|---|---|---|---|
| 1 | 2026-01-01 | North | 120 |
| 2 | 2026-01-02 | South | 80 |
| 3 | 2026-01-03 | North | 150 |
| 4 | 2026-01-04 | South | NULL |
| 5 | 2026-01-05 | North | 90 |
| 6 | 2026-01-06 | South | 200 |
| 7 | 2026-01-07 | North | 110 |
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows by the window's sort order. They differ on ties. ROW_NUMBER never repeats: tied rows get different numbers, in an order you should make deterministic with a tie-breaker. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives the same number and does not skip (1, 2, 2, 3). Ines and Joel tie at 7600.
SELECT full_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, full_name) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC, full_name| full_name | salary | row_num | rnk | dense_rnk |
|---|---|---|---|---|
| Mara Lind | 9200 | 1 | 1 | 1 |
| Ines Cole | 7600 | 2 | 2 | 2 |
| Joel Park | 7600 | 3 | 2 | 2 |
| Tomas Reyes | 6900 | 4 | 4 | 3 |
| Nadia Voss | 6400 | 5 | 5 | 4 |
| Ravi Shah | 6100 | 6 | 6 | 5 |
| Sven Ito | 5200 | 7 | 7 | 6 |
| Lea Brandt | 4800 | 8 | 8 | 7 |
| Owen Hart | 4300 | 9 | 9 | 8 |
| Pia Novak | 3900 | 10 | 10 | 9 |
Return the highest-paid employee in each department.
The classic top-N-per-group pattern: rank inside each department with PARTITION BY, then filter on the rank in an outer query, because a window function cannot be used in WHERE. Use RANK if ties should all be returned and ROW_NUMBER if you want exactly one row per group. For top 3, change = 1 to <= 3.
WITH ranked AS (
SELECT full_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE department_id IS NOT NULL
)
SELECT department_id, full_name, salary
FROM ranked
WHERE rnk = 1
ORDER BY department_id| department_id | full_name | salary |
|---|---|---|
| 1 | Tomas Reyes | 6900 |
| 2 | Mara Lind | 9200 |
| 3 | Sven Ito | 5200 |
Find the second-highest salary.
With windows: DENSE_RANK over salary descending, then keep rank 2. Because it is dense, a tie at the top does not hide the second value. Without windows: the maximum of everything below the maximum. Decide what you would return if there is no second value: the first query then returns no rows, the second returns NULL.
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 2| salary |
|---|
| 7600 |
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)| second_highest |
|---|
| 7600 |
Build a running total. Why do two tied rows get the same total?
When a window has an ORDER BY and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same sort value as peers and includes all of them, so tied rows share a total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add one row at a time. A running total per region, with the NULL amount ignored by SUM:
SELECT region, sold_on, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sold_on
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS region_running
FROM sales
ORDER BY region, sold_on| region | sold_on | amount | region_running |
|---|---|---|---|
| North | 2026-01-01 | 120 | 120 |
| North | 2026-01-03 | 150 | 270 |
| North | 2026-01-05 | 90 | 360 |
| North | 2026-01-07 | 110 | 470 |
| South | 2026-01-02 | 80 | 80 |
| South | 2026-01-04 | NULL | 80 |
| South | 2026-01-06 | 200 | 280 |
Now the tie. Two Engineering salaries are 7600:
SELECT salary,
SUM(salary) OVER (ORDER BY salary) AS default_frame,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM employees
WHERE department_id = 2
ORDER BY salary| salary | default_frame | rows_frame |
|---|---|---|
| 6100 | 6100 | 6100 |
| 7600 | 21300 | 13700 |
| 7600 | 21300 | 21300 |
| 9200 | 30500 | 30500 |
Show each North sale and its change from the previous sale.
LAG(column) reads the value from the previous row in the window's order; the first row has no previous row, so it gets NULL. LEAD reads the next row. Both take an optional offset and a default: LAG(amount, 1, 0).
SELECT sold_on, amount,
amount - LAG(amount) OVER (ORDER BY sold_on) AS change
FROM sales
WHERE region = 'North'
ORDER BY sold_on| sold_on | amount | change |
|---|---|---|
| 2026-01-01 | 120 | NULL |
| 2026-01-03 | 150 | 30 |
| 2026-01-05 | 90 | -60 |
| 2026-01-07 | 110 | 20 |
What share of its department's salary budget does each employee take?
Divide the row's value by an aggregate window over the partition: SUM(salary) OVER (PARTITION BY department_id). Multiply by 100.0, not 100, so SQLite does not use integer division.
SELECT full_name, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct_of_dept
FROM employees
WHERE department_id = 1
ORDER BY salary DESC| full_name | salary | pct_of_dept |
|---|---|---|
| Tomas Reyes | 6900 | 39.2 |
| Nadia Voss | 6400 | 36.4 |
| Owen Hart | 4300 | 24.4 |
Compute a 3-row moving average of sales.
Limit the frame to the current row and the two before it: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The first rows have fewer than three values, and AVG skips NULLs, so the 4th, 5th and 6th averages use only the non-NULL amounts in their frames.
SELECT sold_on, amount,
ROUND(AVG(amount) OVER (ORDER BY sold_on ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3
FROM sales
ORDER BY sold_on| sold_on | amount | avg_3 |
|---|---|---|
| 2026-01-01 | 120 | 120 |
| 2026-01-02 | 80 | 100 |
| 2026-01-03 | 150 | 116.7 |
| 2026-01-04 | NULL | 115 |
| 2026-01-05 | 90 | 120 |
| 2026-01-06 | 200 | 145 |
| 2026-01-07 | 110 | 133.3 |
How far below the best-paid person in the department is everyone else?
An aggregate over the whole partition, without an ORDER BY in the window, is the same value on every row. Subtract the row's own value. The same idea gives percent-of-total and difference-from-average. For splitting rows into equal groups, look at NTILE(n).
SELECT full_name, salary,
MAX(salary) OVER (PARTITION BY department_id) - salary AS gap_to_top
FROM employees
WHERE department_id = 2
ORDER BY salary DESC, full_name| full_name | salary | gap_to_top |
|---|---|---|
| Mara Lind | 9200 | 0 |
| Ines Cole | 7600 | 1600 |
| Joel Park | 7600 | 1600 |
| Ravi Shah | 6100 | 3100 |
Window functions need SQLite 3.25 or newer. They are standard SQL, so the same queries run in PostgreSQL, MySQL 8 and SQL Server.
Keep going
Practice it, don't just read it. The blue belt in Query Belt is all window functions, from ROW_NUMBER to frames, with a check that shows which rows differ when a tie or a frame goes wrong.
Coming to Google Play