Window Functions

By the end of this lesson, you will be able to calculate rankings and running totals within specific groups of data using SQL window functions without collapsing rows.

What it is

Window functions 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 retain all original rows while adding new calculated columns. The "window" is defined by the OVER() clause, which specifies how to partition data (PARTITION BY) and order it (ORDER BY). Common terms include "frame" (the subset of rows considered) and "ranking functions" like RANK(), DENSE_RANK(), and ROW_NUMBER().

Why it matters

• Efficiency: Solves complex analytical problems in a single query rather than using self-joins or subqueries.
• Contextual Analysis: Allows comparison of a value against its neighbors (e.g., month-over-month growth).
• Ranking: Identifies top performers within categories (e.g., highest sales per region).
• Running Totals: Calculates cumulative sums for time-series analysis or inventory tracking.

Syntax or steps

The basic structure is: function_name() OVER (PARTITION BY column ORDER BY column). 1. Choose a function (e.g., SUM(), RANK()). 2. Define the partition with PARTITION BY to group rows independently. 3. Define the order with ORDER BY to determine sequence within partitions. 4. For running totals, the default frame includes all rows from the start of the partition up to the current row.

Example

SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) as running_total
FROM employees;

Explanation:

• RANK() ... PARTITION BY department: Assigns a rank to each employee based on salary, restarting the count for each department. Ties receive the same rank.
• SUM(salary) ... ORDER BY hire_date: Calculates a cumulative sum of salaries within each department, ordered by when employees were hired.
• The result retains every row from the employees table but adds two new analytical columns.

Common mistakes

• Missing ORDER BY: Running totals require an explicit ORDER BY clause; otherwise, the result is non-deterministic or errors out depending on the database engine.
• Confusing RANK vs ROW_NUMBER: RANK() skips numbers after ties (1, 2, 2, 4), while ROW_NUMBER() always increments sequentially (1, 2, 3, 4). Use DENSE_RANK() if you want no gaps (1, 2, 2, 3).
• Filtering before Windowing: You cannot use WHERE to filter results based on a window function alias directly. You must wrap the query in a subquery or CTE and filter in the outer query.

When to use it

Compare window functions with standard aggregates and self-joins.

Method	Best For	Limitation
Window Functions	Row-level analytics, rankings, moving averages.	Can be resource-intensive on very large datasets without proper indexing.
GROUP BY Aggregates	Summary statistics per category (e.g., total sales per region).	Collapses rows; loses individual record detail.
Self-Joins	Comparing rows to other rows (legacy approach).	Complex syntax, harder to maintain, often slower.

Practice

Guided Exercise: Write a query to find the second-highest paid employee in each department. Hint: Use ROW_NUMBER() or RANK() inside a CTE, then filter where the rank equals 2. Challenge: Calculate the percentage of total company revenue contributed by each product category, keeping all product rows visible. Hint: Use SUM(revenue) OVER () for the denominator and SUM(revenue) OVER (PARTITION BY category) for the numerator.

Quick check

Question: If two employees have the exact same salary in a department, what happens to their ranks when using RANK()? Answer: They receive the same rank number, and the next distinct salary receives a rank number skipping the tied positions (e.g., both get Rank 1, next gets Rank 3).

Summary

Window functions enable powerful row-level analytics by calculating values over a defined "window" of related rows without aggregating them away. Mastering PARTITION BY and ORDER BY allows you to efficiently compute rankings, running totals, and relative comparisons directly in your SQL queries.