Window Function Filtering

In this lesson, you will learn how to use window functions to extract "Top-N" records within groups without needing complex subqueries.

The Challenge of "Top-N" per Group

Imagine you have a table of employees, and you want to find the top three highest-paid employees in each department. In the past, you might have tried to solve this using correlated subqueries or tricky joins, which can get messy and slow as your data grows.

The Window Function Approach

Instead of filtering your data first and then joining it, we can use a window function—specifically DENSE_RANK() or ROW_NUMBER()—to "label" each row based on its rank within its group (the department).

The trick is that we cannot filter by these labels directly in the WHERE clause because the WHERE clause is processed before window functions are calculated. To bypass this, we use a Common Table Expression (CTE) to calculate the ranks, and then we filter the result in a main query.

Here is what that looks like:

WITH RankedEmployees AS (
    SELECT 
        employee_name, 
        department, 
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
SELECT *
FROM RankedEmployees
WHERE salary_rank <= 3;

Why This Matters

1. PARTITION BY: This resets the rank for every unique department. It tells SQL: "Treat each department as its own little island."
2. ORDER BY: This dictates how we define the "top" (e.g., highest salary first).
3. The CTE: By calculating the rank in the CTE, we turn a dynamic window calculation into a static column. Now, the final WHERE clause can simply look at the salary_rank column as if it were any other piece of data in the table.

This pattern is significantly cleaner and more efficient than older methods, as it allows the database engine to handle the partitioning and ranking in a single pass of the data.

Key Takeaway: By using a CTE to calculate window-based ranks, you can easily filter for Top-N records per group while keeping your logic readable and performant.