Mastering SQL Window Functions: Rank, Row Number, and Analytics

Window functions perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not collapse rows into a single output row - they retain the details of individual entries while projecting statistical values.

ROW_NUMBER vs RANK vs DENSE_RANK

These three window functions assign integers based on the sort order in the OVER clause. Their behavior on tied values differs:

- ROW_NUMBER(): Assigns unique, sequential numbers starting at 1. Ties are resolved arbitrarily. - RANK(): Assigns duplicate ranks to identical values. If ties exist, it skips subsequent rank numbers. - DENSE_RANK(): Assigns duplicate ranks to identical values, but does not skip any numbers.

-- Compare rankings of employee salaries
SELECT Name, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum,
       RANK() OVER (ORDER BY Salary DESC) as RankVal,
       DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRankVal
FROM Employee;

Analytical Offsets: LEAD and LAG

LEAD and LAG let you access data from relative rows in the window without performing self-joins. LAG retrieves a value from N rows before the current row, and LEAD retrieves a value N rows ahead.

The PARTITION BY Clause: Resetting Windows Per Group

Without PARTITION BY, a window function operates across the entire result set. With PARTITION BY, it resets for each unique value of the partition column. This allows you to compute per-group rankings or running totals without collapsing rows with GROUP BY.

-- Rank employees by salary within each department
SELECT Name, Department, Salary,
       RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as DeptRank
FROM Employee;

The DeptRank column resets to 1 for each new department value. The highest earner in Engineering gets rank 1; the highest earner in Marketing also gets rank 1. Without PARTITION BY, there would be only one rank-1 employee across the entire company.

Running Totals with SUM() OVER

-- Running total of daily sales
SELECT OrderDate, DailySales,
       SUM(DailySales) OVER (
         ORDER BY OrderDate
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as RunningTotal
FROM DailySales;

The ROWS BETWEEN clause defines the window frame - which rows are included in the calculation relative to the current row. UNBOUNDED PRECEDING means 'from the very first row in the partition.' CURRENT ROW means 'up to and including the current row.' This combination produces a running total that accumulates as the ORDER BY date increases.

NTILE: Dividing Data into Buckets

-- Divide customers into 4 spending quartiles
SELECT CustomerID, TotalSpend,
       NTILE(4) OVER (ORDER BY TotalSpend DESC) as SpendQuartile
FROM CustomerSummary;

NTILE(n) divides the result set into n buckets of approximately equal size and assigns each row a bucket number. This is the standard approach for creating quartile or decile rankings in analytical reporting. If the row count is not evenly divisible by n, the earlier buckets receive one extra row.

FIRST_VALUE and LAST_VALUE

FIRST_VALUE() returns the value from the first row of the window frame; LAST_VALUE() returns the value from the last row. A common gotcha: LAST_VALUE() uses the default frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means it returns the current row's value in most cases, not the true last row of the partition. To fix this, explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Common Window Function Interview Question

-- Find the second highest salary in each department
SELECT DISTINCT Department, Salary as SecondHighest
FROM (
  SELECT Department, Salary,
         DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as dr
  FROM Employee
) ranked
WHERE dr = 2;

This is one of the classic window function interview questions. DENSE_RANK() is used instead of RANK() to handle ties correctly - if two employees share the top salary, RANK() would skip rank 2, but DENSE_RANK() would still assign rank 2 to the next distinct salary. The outer query filters to only rows with dr=2, giving the second-highest salary per department.