Window Functions for Partitioned Analysis

What is a Window Function?

First, it's crucial to understand how window functions differ from the aggregate functions (SUM, COUNT, AVG, etc.) you use with GROUP BY.

• An aggregate function collapses multiple rows into a single summary row. If you GROUP BY region and SUM(sales), you get one row of total sales for each region. You lose the individual sales data.
• A window function performs a calculation across a set of rows (the "window") but returns a value for every single row. You get to see the individual row's data and the result of the window calculation alongside it.

The Substack article, "SQL Window Functions Made Simple," puts it nicely: they’re like "peeking over the fence at your neighbor’s yard, seeing the big picture while staying in your row."

The basic syntax always includes the OVER() clause:
FUNCTION_NAME() OVER ( ... )

Let's see how this works by using a familiar aggregate function, MAX(), as a window function.

As you saw, MAX(salary) OVER (PARTITION BY department_name) returns the maximum salary for a given department on every row for employees in that department. This is the core concept: we're adding analytical context without losing the original detail.

Ranking Rows with ROW_NUMBER, RANK, and DENSE_RANK

The most common use of window functions is for ranking. SQL provides three primary functions for this, and the main difference is how they handle ties (i.e., when two rows have the same value in the column you're ordering by).

1. ROW_NUMBER(): Assigns a unique, sequential number to each row. It never repeats numbers, even for ties.
2. RANK(): Assigns the same rank to tied rows. However, it then skips the next rank(s). For example, if two rows are tied for 2nd place, they both get rank 2, and the next rank assigned is 4.
3. DENSE_RANK(): Assigns the same rank to tied rows, but does not skip the next rank. In the same example, two rows get rank 2, and the next rank assigned is 3.

Let's review these three functions in a clear, concise article.

This image provides a perfect visual summary of how the three functions treat ties within different groups.

Choosing the right function depends on your business question:

• Use ROW_NUMBER() if you need a unique number for every row, regardless of ties (e.g., for picking an arbitrary "top N").
• Use RANK() for traditional competition-style ranking where ties share a rank and subsequent positions are skipped.
• Use DENSE_RANK() when you want a gapless ranking, which is often useful for "top N" reports where you want all items at a certain rank level, even if it means including more than N items due to ties.

The Power of Partitioning: Ranking Within Groups

Now we get to the core of today's learning outcome: performing partitioned analysis. This is done by combining PARTITION BY with ORDER BY inside the OVER() clause.

OVER (PARTITION BY column_to_group BY ORDER BY column_to_rank_by)

This tells SQL to:

1. First, split the data into groups or "partitions" based on the PARTITION BY column (e.g., region, department).
2. Then, within each of those partitions, order the rows based on the ORDER BY column (e.g., sales DESC).
3. Finally, apply the window function (e.g., RANK()) to the ordered rows within each partition. The ranking will reset for each new partition.

This is exactly what you need to rank customers within regions.

The query and output below show another perfect example of this in action.

Putting It All Together: A Complete Example

Let's walk through a common business scenario: Find the top 3 highest-paid employees in each department.

To solve this, you need to:

1. Partition the data by department.
2. Order the data by salary in descending order within each department.
3. Rank the employees using RANK() or DENSE_RANK().
4. Filter the results to keep only those with a rank of 3 or less.

This video demonstrates exactly how to do this.

WITH RankedProducts AS (
  SELECT
    product_name,
    category,
    sales_total,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_total DESC) AS sales_rank
  FROM
    product_sales
)

SELECT
  category,
  product_name,
  sales_total,
  sales_rank
FROM
  RankedProducts
WHERE
  sales_rank <= 2;

Conclusion

Congratulations! You've just unlocked a new tier of analytical capabilities in SQL. Window functions are essential for any data analyst, allowing you to create sophisticated rankings, comparisons, and aggregations that are simply not possible with GROUP BY alone.

Key Takeaways:

• Window vs. Aggregate: Window functions perform calculations across a "window" of rows but preserve the individual rows, unlike GROUP BY which collapses them.
• The OVER Clause: This is the heart of a window function. OVER(PARTITION BY ... ORDER BY ...) defines how the window is constructed.
• Partitioning is Key: PARTITION BY divides your data into subgroups, and the window function restarts for each subgroup. This is how you analyze data "within" a category.
• Ranking Functions:
  • ROW_NUMBER(): Unique, sequential numbers.
  • RANK(): Handles ties by skipping ranks.
  • DENSE_RANK(): Handles ties without skipping ranks.
• CTEs and Window Functions: They are a powerful combination. Use a CTE to perform your window calculation and then SELECT from the CTE to filter, aggregate, or join the results.

Next Lesson Preview:

We've focused on ranking functions today, but the OVER() clause can be used with many other functions. In our next lesson, we will continue exploring window functions to calculate running totals and moving averages. This will enable you to analyze trends over time, such as calculating month-over-month growth or 7-day rolling average sales, all directly within SQL.