Autocoder

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 inPostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+
Sample dataemployees, logins, contacts
LevelEasy 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.

sql
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

SQL Window Functions Interview Questions: questions with worked answers

  1. 1

    Show the difference between ROW_NUMBER, RANK and DENSE_RANK on tied salaries.

    easy

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

    sql
    SELECT 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   | 5
  2. 2

    Find the highest-paid employee in each department.

    medium

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

    sql
    SELECT 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   | 50000
  3. 3

    Compute a running total of salary ordered by employee id.

    medium

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

    sql
    SELECT 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  | 450000
  4. 4

    Explain ROWS versus RANGE with an example where they differ.

    hard

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

    sql
    SELECT 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  | 450000
  5. 5

    Use LAG and LEAD to compare each salary with the previous and next employee.

    medium

    LAG(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.

    sql
    SELECT 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        | -35000
  6. 6

    Show each employee salary beside the department average and the difference.

    medium

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

    sql
    SELECT 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    | 0
  7. 7

    What does NTILE do? Split employees into 3 salary groups.

    medium

    NTILE(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.

    sql
    SELECT 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  | 3
  8. 8

    Compute a 3-row moving average.

    medium

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

    sql
    SELECT 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  | 55000
  9. 9

    Why does LAST_VALUE return the current row instead of the last row, and how do you fix it?

    hard

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

    sql
    SELECT 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       | Gita
  10. 10

    Remove duplicate rows, keeping the latest per email.

    medium

    Number 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).

    sql
    CREATE 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');
    sql
    SELECT 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-01
  11. 11

    Find users with 3 or more consecutive login days (gaps and islands).

    hard

    Subtract 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).

    sql
    CREATE 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');
    sql
    SELECT 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 | 3
  12. 12

    Show each employee salary as a percentage of the department total.

    medium

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

    sql
    SELECT 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  | 100
  13. 13

    Why can you not use a window function in WHERE, and what is QUALIFY?

    medium

    Logical 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. 14

    What is the difference between a window function and GROUP BY?

    medium

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

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

See all in this section