Query Belt Coming to Google Play

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.

By Andrii Boiko · Updated 2026-11-18 · Every result on this page was produced by running the query on the sample database below in SQLite 3.37.2.

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

idnamebudget
1Sales30000
2Engineering80000
3Support20000
4Finance15000

employees

idfull_namedepartment_idmanager_idsalaryhired_on
1Mara Lind2NULL92002019-03-04
2Joel Park2176002020-06-15
3Ines Cole2176002021-01-11
4Tomas Reyes1569002018-09-01
5Nadia Voss1NULL64002017-05-22
6Owen Hart1543002022-02-14
7Pia Novak3839002023-04-03
8Sven Ito3NULL52002020-11-30
9Lea BrandtNULLNULL48002024-01-08
10Ravi Shah2161002022-08-19

sales

idsold_onregionamount
12026-01-01North120
22026-01-02South80
32026-01-03North150
42026-01-04SouthNULL
52026-01-05North90
62026-01-06South200
72026-01-07North110

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_namesalaryrow_numrnkdense_rnk
Mara Lind9200111
Ines Cole7600222
Joel Park7600322
Tomas Reyes6900443
Nadia Voss6400554
Ravi Shah6100665
Sven Ito5200776
Lea Brandt4800887
Owen Hart4300998
Pia Novak390010109
Result: 10 rows

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_idfull_namesalary
1Tomas Reyes6900
2Mara Lind9200
3Sven Ito5200
Result: 3 rows

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
Result: 1 row
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
second_highest
7600
Result: 1 row

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
regionsold_onamountregion_running
North2026-01-01120120
North2026-01-03150270
North2026-01-0590360
North2026-01-07110470
South2026-01-028080
South2026-01-04NULL80
South2026-01-06200280
Result: 7 rows

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
salarydefault_framerows_frame
610061006100
76002130013700
76002130021300
92003050030500
Result: 4 rows

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_onamountchange
2026-01-01120NULL
2026-01-0315030
2026-01-0590-60
2026-01-0711020
Result: 4 rows

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_namesalarypct_of_dept
Tomas Reyes690039.2
Nadia Voss640036.4
Owen Hart430024.4
Result: 3 rows

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_onamountavg_3
2026-01-01120120
2026-01-0280100
2026-01-03150116.7
2026-01-04NULL115
2026-01-0590120
2026-01-06200145
2026-01-07110133.3
Result: 7 rows

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_namesalarygap_to_top
Mara Lind92000
Ines Cole76001600
Joel Park76001600
Ravi Shah61003100
Result: 4 rows

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