SQL window function interview questions, with answers

The questions, with answers

1. 1.What is a window function, and how is it different from GROUP BY?

   A window function computes a value across a set of rows related to the current row — a "window" — and returns that value on every row, without collapsing them. GROUP BY collapses many rows into one per group; a window function keeps every row and adds a column. So "total salary of each department" is GROUP BY; "each employee, alongside their department's total" is a window function. The OVER clause is what makes a function a window function.

   SELECT name, dept_id, salary,
          SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
   FROM Employees;
   -- one row per employee, dept_total repeated on each

2. 2.What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

   All three number rows in the order given by ORDER BY inside OVER; they differ only on ties. ROW_NUMBER never ties — equal values still get consecutive distinct numbers, in an unspecified order between them. RANK gives tied rows the same rank and then skips: 1, 1, 3. DENSE_RANK gives tied rows the same rank and does not skip: 1, 1, 2.

   With salaries 100, 100, 90: ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2. So "the second-highest salary" is DENSE_RANK = 2, not RANK = 2 — with RANK there may be no row numbered 2 at all.

   SELECT name, salary,
          ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
          RANK()       OVER (ORDER BY salary DESC) AS rnk,
          DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
   FROM Employees;

3. 3.What does PARTITION BY do?

   PARTITION BY splits the rows into groups, and the window function restarts for each group — like GROUP BY, but without collapsing rows. ORDER BY inside OVER then orders rows within each partition. Without PARTITION BY, the whole result set is one window. "Rank employees by salary within their department" is PARTITION BY dept_id ORDER BY salary DESC; each department gets its own 1, 2, 3.

   SELECT name, dept_id, salary,
          DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
   FROM Employees;

4. 4.How do you find the second-highest salary in each department?

   Rank within each department with DENSE_RANK, then keep rank 2. DENSE_RANK, not RANK: if two people share the top salary, RANK numbers them 1, 1 and the next person 3, so filtering on RANK = 2 returns nothing. And because a window function can't appear in WHERE, the ranking goes in a subquery or CTE and the filter goes outside it.

   One more edge to mention: a department with a single distinct salary has no rank 2 and simply doesn't appear — which is correct, and worth saying out loud.

   WITH ranked AS (
     SELECT name, dept_id, salary,
            DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drnk
     FROM Employees
   )
   SELECT name, dept_id, salary
   FROM ranked
   WHERE drnk = 2;

5. 5.Why can't you use a window function in a WHERE clause?

   Because of the logical order in which a query is evaluated: FROM, then WHERE, then GROUP BY and HAVING, and only then the SELECT list — which is where window functions are computed. WHERE runs before the window values exist, so referring to one there is an error. The same applies to HAVING. The fix is always the same: compute the window function in a subquery or CTE and filter in the outer query. Some databases add a QUALIFY clause (Snowflake, BigQuery, Teradata, DuckDB) that filters on window results directly, but it is not standard and MySQL and PostgreSQL don't have it.

6. 6.What are LAG and LEAD used for?

   LAG returns a value from a previous row in the window order; LEAD from a following row. They are how you compare a row with its neighbour without a self join — month-over-month change, time since the previous login, whether a price went up or down. Both take an offset (default 1) and a default value for when there is no such row (default NULL), so LAG(amount, 1, 0) gives 0 on the first row instead of NULL.

   SELECT month, revenue,
          revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS change_from_prev
   FROM MonthlyRevenue;

7. 7.How do you write a running total, and what is the frame trap?

   SUM(amount) OVER (ORDER BY order_date) gives a cumulative sum. The trap is the default frame: when OVER has an ORDER BY but no explicit frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats rows with the same ORDER BY value as peers — they all get the same running total, which includes every one of them. Two orders on the same date both show the total through that whole date, not one after the other.

   If you want a true row-by-row running total, say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or order by something unique (add order_id as a tiebreaker).

   SELECT order_id, order_date, amount,
          SUM(amount) OVER (ORDER BY order_date, order_id
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
   FROM Orders;

8. 8.Which databases support window functions?

   PostgreSQL (since 8.4), SQL Server (ranking functions since 2005, the full set including LAG/LEAD and frames since 2012), Oracle (for many years, where they're called analytic functions), SQLite (since 3.25), and MySQL only from 8.0 — MySQL 5.7 has none, which is why older tutorials show variable tricks with @row instead. If a question mentions MySQL, ask which version; it changes the answer.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 4 on window functions and 30 across SQL.

SELECT name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank FROM Employees;