SQL Window Function Interview Questions

Q1: ROW_NUMBER vs RANK vs DENSE_RANK with ties

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM results;
-- Bob and Cara are tied at 90

Bob and Cara tie at 90 in all three — the divergence shows up one row later, at Dee. ROW_NUMBER() never repeats a number (4). RANK() repeats 2 for the tie, then skips to 4 for the next row, leaving a gap for the two rows that shared rank 2. DENSE_RANK() also repeats 2, but the next distinct value gets 3, with no gap at all. Full walkthrough with more examples in ROW_NUMBER vs RANK vs DENSE_RANK.

Q2: Window function vs GROUP BY — what actually differs?

-- GROUP BY: one row per department
SELECT dept, AVG(salary) AS avg_salary
FROM employees GROUP BY dept;

-- Window function: every employee row, plus the department average
SELECT name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS avg_salary
FROM employees;

This is the core conceptual difference tested in almost every window function question: GROUP BY answers "what's the average per department," discarding individual employees. A window function answers "what's each employee's salary and their department's average," in the same row — which is exactly what's needed to compare an individual to their group.

Q3: PARTITION BY vs GROUP BY

PARTITION BY is not a replacement for GROUP BY — it defines the "window" a function like AVG() or ROW_NUMBER() operates over, without collapsing any rows, as shown directly in Q2. Combine it with ORDER BY inside the same OVER() clause to also control the row-by-row sequence within each partition, which matters for ranking and running-total functions.

Q4: Calculate a running total

SELECT order_date, total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

Adding ORDER BY inside OVER() changes the default frame from "the whole partition" to "everything from the start up through the current row" — that's precisely what makes it a running total instead of a flat grand total repeated on every row.

Q5: Compare each row to the previous one with LAG

SELECT order_date, total,
  total - LAG(total) OVER (ORDER BY order_date) AS change_from_prev
FROM daily_sales;

LAG() reaches back to the previous row's value within the same ordered window, and LEAD() reaches forward — both return NULL at the boundary (the first row has no previous value; the last has no next), which is expected, not an error.

Q6: Why can't WHERE filter a window function directly?

-- Error: window functions are not allowed in WHERE
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn = 1;

-- Correct: wrap it in a subquery or CTE first
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn = 1;

Window functions are logically computed alongside SELECT, which runs after WHERE in the standard processing order (see SQL Execution Order Explained) — the same reason a SELECT alias isn't visible in WHERE, detailed in SQL Alias Scope Explained. Wrapping the window function in a subquery or CTE makes it a real, already-computed column by the time the outer WHERE runs.