SQL Window Functions

Master SQL window functions to perform analytics like ranking, running totals, and comparing rows without collapsing your dataset.

What it is

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions (like SUM() or COUNT()) which collapse multiple rows into one, window functions return a value for each input row while preserving the original row structure. Key concepts include the OVER() clause, which defines the "window" or subset of rows to operate on, and partitioning, which divides data into groups similar to GROUP BY.

Why it matters

• Ranking: Assign positions within groups (e.g., top 3 sales per region).
• Trend Analysis: Compare current values with previous or next rows using LAG and LEAD.
• Running Totals: Calculate cumulative sums or averages over time.
• Deduplication: Identify and remove duplicate records based on specific criteria.
• Efficiency: Perform complex analytics in a single query pass rather than using self-joins or subqueries.

Syntax or steps

The basic syntax involves calling a function followed by the OVER clause. Inside OVER, you can specify PARTITION BY to group rows and ORDER BY to sort them within those groups.

function_name() OVER (
    PARTITION BY column1
    ORDER BY column2
)

Example

This example calculates employee rankings by salary within departments and compares salaries to the previous hire date.

SELECT 
    department,
    employee_name,
    hire_date,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_by_salary,
    LAG(salary, 1) OVER (PARTITION BY department ORDER BY hire_date) as prev_employee_salary
FROM employees;

Explanation:

• ROW_NUMBER()...: Assigns a unique sequential integer to rows within each department, ordered by highest salary first.
• PARTITION BY department: Resets the numbering for each department independently.
• LAG(salary, 1)...: Retrieves the salary from the previous row (ordered by hire date) within the same department.

Common mistakes

• Confusing RANK vs DENSE_RANK: RANK() skips numbers after ties (1, 2, 2, 4), while DENSE_RANK() does not (1, 2, 2, 3). Choose based on whether gaps matter.
• Missing ORDER BY: Functions like ROW_NUMBER() require an ORDER BY clause inside OVER() to be deterministic; otherwise, results may vary randomly.
• Using Window Functions in WHERE: You cannot filter directly on a window function result in the WHERE clause. Use a Common Table Expression (CTE) or subquery instead.
• Ignoring NULLs in LAG/LEAD: If the previous row has a NULL value, LAG returns NULL. Handle this with COALESCE if needed.

When to use it

Practice

Guided Exercise: Write a query to find the second-highest paid employee in each department using DENSE_RANK(). Filter the result to show only rank 2.

Challenge: Calculate the percentage change in salary compared to the previous hire in the same department using LAG().

Hint: Use (current - lagged) / lagged * 100 and handle division by zero.

Quick check

Q: Why can't you write WHERE ROW_NUMBER() = 1 directly?

A: Window functions are evaluated after the WHERE clause. You must wrap the query in a CTE or subquery to filter on the calculated column.

Summary

SQL window functions extend analytical capabilities by allowing row-level calculations across partitions without aggregating data. They are essential for ranking, trend analysis, and efficient deduplication in modern data science workflows.