Window Functions — ROW_NUMBER, RANK, DENSE_RANK

-- GROUP BY collapses rows into groups:
SELECT department, AVG(salary)
FROM employees GROUP BY department;
-- Result: 1 row per department

-- Window function keeps all rows AND adds the aggregate:
SELECT name, department,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Result: all employee rows + their dept average

Window functions compute across a "window" of rows related to the current row, without collapsing the result. They use OVER() clause with optional PARTITION BY and ORDER BY.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

ROW_NUMBER() assigns 1, 2, 3... with no ties. Even if two employees have the same salary, they get different row numbers (arbitrary tiebreaker).

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Example output:
-- Priya   90000   1   1
-- Rahul   85000   2   2
-- Amit    85000   2   2   <- tie
-- Sneha   70000   4   3   <- RANK skips 3, DENSE_RANK doesn't

RANK skips numbers after a tie (1,2,2,4). DENSE_RANK never skips (1,2,2,3). For "find the 2nd highest salary" questions, DENSE_RANK is usually correct.

WITH ranked AS (
  SELECT name, department, salary,
         RANK() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk = 1;

PARTITION BY department restarts the ranking for each department. This is the cleanest way to get top-N per group — far better than correlated subqueries.

SELECT name, department, hire_date,
       ROW_NUMBER() OVER (
         PARTITION BY department
         ORDER BY hire_date ASC
       ) AS join_order
FROM employees;

PARTITION BY resets the counter for each department. This tells you who was hired 1st, 2nd, 3rd in each department — useful for tenure analysis.

WITH ranked AS (
  SELECT name, department, salary,
         DENSE_RANK() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS dr
  FROM employees
)
SELECT name, department, salary
FROM ranked WHERE dr = 2;

Using DENSE_RANK ensures tied salaries are handled correctly. This is one of the most asked SQL interview questions at product companies in Delhi NCR.