Level Up Your SQL: A Simple Guide to Window Functions

SQL window functions are a powerful feature that performs calculations across a set of table rows related to the current row. Unlike a GROUP BY aggregate which collapses rows into a single output, a window function returns a value for every single row, enabling advanced analyses like ranking, running totals, and period-over-period comparisons.

The Core Idea: Looking Beyond the Current Row 🧐

Imagine you have a list of employee salaries.

• A normal function like MAX(salary) would find the single highest salary in the entire table.
• A GROUP BY department would find the max salary for each department, collapsing the data.
• A window function can answer the question, “How does each employee’s salary compare to the average salary of their specific department?” It gives you an answer for every single employee row without losing the original data.

Analogy: You in a Line 🧍‍♂️🧍‍♀️🧍 A regular SQL function only knows about you. A GROUP BY function knows the average height of everyone in the line. A window function is like being able to look at the people immediately in front of and behind you to calculate something new, like the difference in height between you and the person ahead.

Common and Useful Window Functions

All window functions use the OVER() clause to define the “window” or group of rows to consider.

1. Ranking Functions (RANK(), DENSE_RANK(), ROW_NUMBER())

These are used to rank rows within a specific group.

• Use Case: “Rank products by sales volume within each product category.”
• ROW_NUMBER(): Assigns a unique, sequential number to each row.
• RANK(): Assigns ranks, but leaves gaps after ties (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns ranks with no gaps after ties (e.g., 1, 2, 2, 3).

2. Offset Functions (LAG(), LEAD())

These functions allow you to “peek” at the values in a preceding or succeeding row.

• Use Case: “For each month, calculate the sales growth compared to the previous month.”
• LAG(): Accesses data from a previous row in the window.
• LEAD(): Accesses data from a following row in the window.

3. Aggregate Functions as Window Functions (SUM(), AVG())

You can use standard aggregate functions to create running totals and moving averages.

• Use Case: “Calculate the running total of sales for the year with each new sale.”
• How it works: By using SUM(sales) OVER (ORDER BY date), the function sums up all sales from the beginning of the window up to the current row.

Step 2: Offer Next Step

The guide to SQL window functions is now complete. The next topic on our list is about whether Excel is still relevant for data analysis. Shall I prepare that for you?