SQL Window Functions Interview Questions
Last updated 2026-11-19
Window functions separate average SQL candidates from strong ones, and they dominate analyst and backend interviews: rank within a group, running totals, previous-row comparisons and de-duplication. They compute a value for each row using a related set of rows (the window) without collapsing the rows the way GROUP BY does.
Every result on this page was produced by running the query on the sample data shown. The syntax is standard and works in PostgreSQL, MySQL 8.0 and later, SQL Server, Oracle and SQLite 3.25 and later. Related pages: SQL group by and having, SQL CTEs and how to crack a data analyst interview.
| Works in | PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+ |
|---|---|
| Sample data | employees, logins, contacts |
| Level | Easy to hard |
Sample data
The employees table is used for ranking and running totals. Note the ties: Bilal and Farid both earn 75000, and Chitra and Dev both earn 60000.
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);The anatomy of a window
- function() OVER (PARTITION BY a ORDER BY b ROWS BETWEEN x AND y).
- PARTITION BY splits the rows into groups, and omitting it makes the whole result one window.
- ORDER BY inside OVER sets the order for ranking and running calculations.
- The frame (ROWS or RANGE) says which rows around the current one the aggregate sees.
- Window functions run after WHERE, GROUP BY and HAVING, so they cannot appear in WHERE.
SQL Window Functions Interview Questions: questions with worked answers
1 Show the difference between ROW_NUMBER, RANK and DENSE_RANK on tied salaries.
easyROW_NUMBER gives unique consecutive numbers and breaks ties arbitrarily unless you add a tie-breaker. RANK gives tied rows the same rank and then skips numbers (1, 2, 2, 4). DENSE_RANK gives ties the same rank without gaps (1, 2, 2, 3). Look at the 75000 and 60000 ties in the result.
sqlSELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk FROM employees ORDER BY salary DESC, emp_id; -- name | salary | rn | rnk | drnk -- -------+--------+----+-----+----- -- Asha | 90000 | 1 | 1 | 1 -- Bilal | 75000 | 2 | 2 | 2 -- Farid | 75000 | 3 | 2 | 2 -- Chitra | 60000 | 4 | 4 | 3 -- Dev | 60000 | 5 | 4 | 3 -- Esha | 50000 | 6 | 6 | 4 -- Gita | 40000 | 7 | 7 | 52 Find the highest-paid employee in each department.
mediumRank inside each department with PARTITION BY, then keep rank 1 in an outer query, because a window function cannot be used in WHERE. Use RANK if ties should all appear, ROW_NUMBER if exactly one row per department is needed. Engineering has Asha alone at the top, while Sales has two people tied at 60000, so RANK returns both.
sqlSELECT dept_id, name, salary FROM ( SELECT dept_id, name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees WHERE dept_id IS NOT NULL ) t WHERE r = 1 ORDER BY dept_id; -- dept_id | name | salary -- --------+--------+------- -- 10 | Chitra | 60000 -- 10 | Dev | 60000 -- 20 | Asha | 90000 -- 30 | Esha | 500003 Compute a running total of salary ordered by employee id.
mediumSUM over an ordered window defaults to a frame from the start to the current row. Because the default frame is RANGE, peers with equal ORDER BY values are included together, so with ties in the ordering column the running total jumps for all tied rows at once. Ordering by a unique column avoids this, and writing ROWS explicitly is safer.
sqlSELECT emp_id, name, salary, SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running FROM employees ORDER BY emp_id; -- emp_id | name | salary | running -- -------+--------+--------+-------- -- 1 | Asha | 90000 | 90000 -- 2 | Bilal | 75000 | 165000 -- 3 | Chitra | 60000 | 225000 -- 4 | Dev | 60000 | 285000 -- 5 | Esha | 50000 | 335000 -- 6 | Farid | 75000 | 410000 -- 7 | Gita | 40000 | 4500004 Explain ROWS versus RANGE with an example where they differ.
hardROWS counts physical rows relative to the current row. RANGE uses the value of the ORDER BY column and includes all peer rows with the same value. When salaries tie, a RANGE running sum adds all tied rows together at the first of them, whereas ROWS adds one row at a time. The two columns differ exactly on the tied 60000 rows.
sqlSELECT name, salary, SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows, SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range FROM employees ORDER BY salary, emp_id; -- name | salary | by_rows | by_range -- -------+--------+---------+--------- -- Gita | 40000 | 40000 | 40000 -- Esha | 50000 | 90000 | 90000 -- Chitra | 60000 | 150000 | 210000 -- Dev | 60000 | 210000 | 210000 -- Bilal | 75000 | 285000 | 360000 -- Farid | 75000 | 360000 | 360000 -- Asha | 90000 | 450000 | 4500005 Use LAG and LEAD to compare each salary with the previous and next employee.
mediumLAG(col, n, default) reads the value n rows before in the window order and LEAD reads n rows after. The first row has no previous value, so you get NULL unless you pass a default. Typical uses are month-over-month change, time between events and detecting direction changes.
sqlSELECT emp_id, name, salary, LAG(salary) OVER (ORDER BY emp_id) AS prev_salary, LEAD(salary) OVER (ORDER BY emp_id) AS next_salary, salary - LAG(salary, 1, salary) OVER (ORDER BY emp_id) AS change FROM employees ORDER BY emp_id; -- emp_id | name | salary | prev_salary | next_salary | change -- -------+--------+--------+-------------+-------------+------- -- 1 | Asha | 90000 | NULL | 75000 | 0 -- 2 | Bilal | 75000 | 90000 | 60000 | -15000 -- 3 | Chitra | 60000 | 75000 | 60000 | -15000 -- 4 | Dev | 60000 | 60000 | 50000 | 0 -- 5 | Esha | 50000 | 60000 | 75000 | -10000 -- 6 | Farid | 75000 | 50000 | 40000 | 25000 -- 7 | Gita | 40000 | 75000 | NULL | -350006 Show each employee salary beside the department average and the difference.
mediumAn aggregate with OVER (PARTITION BY ...) attaches the group value to every row without collapsing rows, which a GROUP BY cannot do in one query. Employees with a NULL department form their own partition, so Gita is compared with herself.
sqlSELECT name, dept_id, salary, ROUND(AVG(salary) OVER (PARTITION BY dept_id), 0) AS dept_avg, salary - ROUND(AVG(salary) OVER (PARTITION BY dept_id), 0) AS diff FROM employees ORDER BY emp_id; -- name | dept_id | salary | dept_avg | diff -- -------+---------+--------+----------+------ -- Asha | 20 | 90000 | 80000 | 10000 -- Bilal | 20 | 75000 | 80000 | -5000 -- Chitra | 10 | 60000 | 60000 | 0 -- Dev | 10 | 60000 | 60000 | 0 -- Esha | 30 | 50000 | 50000 | 0 -- Farid | 20 | 75000 | 80000 | -5000 -- Gita | NULL | 40000 | 40000 | 07 What does NTILE do? Split employees into 3 salary groups.
mediumNTILE(n) divides the ordered rows into n buckets of near-equal size. With 7 rows and 3 buckets the sizes are 3, 2 and 2: the leading buckets receive the extra rows. It is used for quartiles, deciles and splitting work into batches. Ties are not kept together, so equal salaries can land in different buckets.
sqlSELECT name, salary, NTILE(3) OVER (ORDER BY salary DESC, emp_id) AS bucket FROM employees ORDER BY salary DESC, emp_id; -- name | salary | bucket -- -------+--------+------- -- Asha | 90000 | 1 -- Bilal | 75000 | 1 -- Farid | 75000 | 1 -- Chitra | 60000 | 2 -- Dev | 60000 | 2 -- Esha | 50000 | 3 -- Gita | 40000 | 38 Compute a 3-row moving average.
mediumUse an explicit ROWS frame of the current row and the two before it. The first two rows average over fewer rows because the frame is cut off at the start; filter them out with ROW_NUMBER if you need full windows only.
sqlSELECT emp_id, salary, ROUND(AVG(salary) OVER (ORDER BY emp_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg FROM employees ORDER BY emp_id; -- emp_id | salary | moving_avg -- -------+--------+----------- -- 1 | 90000 | 90000 -- 2 | 75000 | 82500 -- 3 | 60000 | 75000 -- 4 | 60000 | 65000 -- 5 | 50000 | 56666.7 -- 6 | 75000 | 61666.7 -- 7 | 40000 | 550009 Why does LAST_VALUE return the current row instead of the last row, and how do you fix it?
hardWith an ORDER BY the default frame ends at the current row, so LAST_VALUE sees only rows up to the current one and returns the current value. Extend the frame to the end of the partition. FIRST_VALUE does not have this problem, because the frame starts at the beginning.
sqlSELECT name, salary, LAST_VALUE(name) OVER (ORDER BY emp_id) AS wrong_last, LAST_VALUE(name) OVER (ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS real_last FROM employees ORDER BY emp_id; -- name | salary | wrong_last | real_last -- -------+--------+------------+---------- -- Asha | 90000 | Asha | Gita -- Bilal | 75000 | Bilal | Gita -- Chitra | 60000 | Chitra | Gita -- Dev | 60000 | Dev | Gita -- Esha | 50000 | Esha | Gita -- Farid | 75000 | Farid | Gita -- Gita | 40000 | Gita | Gita10 Remove duplicate rows, keeping the latest per email.
mediumNumber the rows within each duplicate group, newest first, and keep row number 1. This works in every database. To delete instead, wrap it in a CTE or subquery and delete where rn > 1 (syntax differs: PostgreSQL can DELETE USING a CTE, MySQL 8 needs a derived-table join).
sqlCREATE TABLE contacts (id INT, email VARCHAR(30), created DATE); INSERT INTO contacts VALUES (1,'a@x.com','2026-01-01'),(2,'a@x.com','2026-02-01'),(3,'b@x.com','2026-01-15'),(4,'a@x.com','2026-03-01');sqlSELECT id, email, created FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created DESC) AS rn FROM contacts ) t WHERE rn = 1 ORDER BY id; -- id | email | created -- ---+---------+----------- -- 3 | b@x.com | 2026-01-15 -- 4 | a@x.com | 2026-03-0111 Find users with 3 or more consecutive login days (gaps and islands).
hardSubtract the row number from the date: within a run of consecutive days the difference is constant, so grouping on it identifies each streak. Use DENSE_RANK or ROW_NUMBER over distinct dates, since duplicate days would break the trick. The date arithmetic differs: SQLite and the query below use julianday, PostgreSQL uses login_date - rn * INTERVAL '1 day' or a date minus integer, and MySQL uses DATE_SUB(login_date, INTERVAL rn DAY).
sqlCREATE TABLE logins (user_id INT, login_date DATE); INSERT INTO logins VALUES (1,'2026-03-01'),(1,'2026-03-02'),(1,'2026-03-03'),(1,'2026-03-07'),(1,'2026-03-08'),(2,'2026-03-02'),(2,'2026-03-04');sqlSELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days FROM ( SELECT user_id, login_date, julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp FROM logins ) t GROUP BY user_id, grp HAVING COUNT(*) >= 3; -- user_id | streak_start | streak_end | days -- --------+--------------+------------+----- -- 1 | 2026-03-01 | 2026-03-03 | 312 Show each employee salary as a percentage of the department total.
mediumDivide by SUM over the same partition. Multiply by 100.0 (not 100) before dividing so integer division does not truncate to zero in PostgreSQL and SQL Server. MySQL and SQLite divide with decimals already, but the habit prevents bugs when you change dialect.
sqlSELECT name, dept_id, salary, ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept_id), 1) AS pct FROM employees WHERE dept_id IS NOT NULL ORDER BY dept_id, emp_id; -- name | dept_id | salary | pct -- -------+---------+--------+----- -- Chitra | 10 | 60000 | 50 -- Dev | 10 | 60000 | 50 -- Asha | 20 | 90000 | 37.5 -- Bilal | 20 | 75000 | 31.3 -- Farid | 20 | 75000 | 31.3 -- Esha | 30 | 50000 | 10013 Why can you not use a window function in WHERE, and what is QUALIFY?
mediumLogical order is FROM, WHERE, GROUP BY, HAVING, then window functions, then SELECT output, ORDER BY. At the point WHERE runs, window values do not exist yet. Filter by wrapping in a subquery or CTE. Snowflake, BigQuery and Teradata offer QUALIFY to filter on window results directly, but PostgreSQL and MySQL do not.
14 What is the difference between a window function and GROUP BY?
mediumGROUP BY collapses rows into one row per group, and you can select only the group keys and aggregates. A window function keeps every input row and adds a computed column, so you can show detail and group-level values side by side. You can combine them: aggregate first with GROUP BY, then apply a window over the grouped result, for example ranking departments by total salary.
sqlSELECT dept_id, SUM(salary) AS total, RANK() OVER (ORDER BY SUM(salary) DESC) AS r FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id ORDER BY r; -- dept_id | total | r -- --------+--------+-- -- 20 | 240000 | 1 -- 10 | 120000 | 2 -- 30 | 50000 | 3
Frequently asked questions
Which databases support window functions?
PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2005+, Oracle 8i+ and SQLite 3.25+. MySQL 5.7 does not, so older interview environments may need user variables instead.
Should I use RANK or DENSE_RANK for the Nth highest salary?
DENSE_RANK, because it does not skip numbers after ties. See Nth highest salary questions.
Do window functions slow queries down?
They need a sort per distinct window specification, so reuse the same OVER clause and index the partition and order columns where possible.
Can I use window functions in UPDATE or DELETE?
Not directly. Compute them in a subquery or CTE and join back, as in the de-duplication example.
Related guides
More in Programming Language Interview Questions by Language
- TypeScript Interview Questions and Answers
- .NET Core and .NET 8 Interview Questions
- Advanced Python Interview Questions
- ASP.NET Core MVC Interview Questions
- C Language Interview Questions and Answers
- C Pointers Interview Questions
- C Programming Output Questions with Answers
- C# Async Await Interview Questions
- C# Coding Interview Questions
- C# Collections Interview Questions
- C# Delegates and Events Interview Questions
- C# Interview Questions and Answers