Filtering Results After Windowing

In this lesson, you will learn why you cannot filter the results of a window function directly in a WHERE clause and how to overcome this using subqueries and Common Table Expressions (CTEs).

The "Order of Operations" Problem

In SQL, the WHERE clause is processed before window functions are calculated. This means if you try to write something like SELECT RANK() OVER(...) as rnk FROM table WHERE rnk > 5, the database will throw an error. It doesn't know what rnk is yet because the window calculation hasn't happened!

To filter based on a windowed value, we must perform the calculation first and then filter the results in a separate step.

Using a CTE for Clarity

A Common Table Expression (CTE) is the cleanest way to handle this. You treat the window function like a "temporary table" that you can query again.

Imagine you want to find employees who have a salary higher than the average salary of their specific department.

WITH SalaryStats AS (
    SELECT 
        employee_name, 
        department, 
        salary,
        AVG(salary) OVER(PARTITION BY department) as dept_avg
    FROM employees
)
SELECT * 
FROM SalaryStats 
WHERE salary > dept_avg;

How it Works

1. The Inner Query (The CTE): The database first calculates the dept_avg for every row. At this stage, the window function is fully materialized.
2. The Outer Query: Because the SalaryStats result is now effectively a "table," the WHERE clause is applied to the final result set. Since dept_avg already exists as a column, the database can easily compare it against the salary column.

You can achieve the same result using a subquery (a SELECT inside a FROM clause), but CTEs are generally preferred in professional environments because they make your logic easier to read and debug. By separating the analytical calculation from the data filtering, you avoid the architectural traps of the SQL execution order and get exactly the rows you need.

Key Takeaway: Because the WHERE clause executes before window functions, you must wrap your window calculation in a CTE or subquery so that the filter applies to the already-calculated results.