In this lesson, you will learn how to use subqueries to "lock in" analytical window calculations so they can be treated as simple, static columns for downstream logic.
The "Layering" Problem
When you write complex analytical queries, you often face a limitation: the WHERE clause is processed before window functions. This makes it impossible to filter your results based on a window calculation (like RANK() or SUM(...) OVER(...)) directly in the same block of code. Furthermore, as your pipelines grow, performing multiple analytical operations in one scope can make your logic messy and harder for the optimizer to handle.
Encapsulating with Subqueries
By placing your window function inside a subquery, you "materialize" the analytical result into a virtual table. Once the subquery computes the windowing logic, the outer query treats those results as standard, raw data. This allows you to perform additional operations—like filtering, joining, or further aggregation—on values that are already computed.
Think of it as creating a "snapshot" of your analytical work before moving on to the next step of your analysis.
Concrete Illustration
Imagine you want to find employees who have a salary higher than the average salary of their specific department. If you try to compare a column to a window function in a WHERE clause, the database will throw an error. Instead, you isolate the window function in a subquery:
SELECT employee_name, salary, dept_avg
FROM (
SELECT
employee_name,
salary,
AVG(salary) OVER(PARTITION BY department_id) as dept_avg
FROM employees
) as salary_stats
WHERE salary > dept_avg;
In this example, the subquery (salary_stats) performs the windowing operation to calculate the dept_avg for every row. By the time the outer SELECT runs, the "Analytical Frame" is already calculated. The outer query now sees dept_avg as a fixed column, allowing you to easily filter the final dataset with a simple WHERE clause. This encapsulation isolates your logic, keeping the "heavy lifting" of the window calculation distinct from the "selection" of your final report.
Key Takeaway: Windowing within a subquery creates a clean separation between analytical computation and final data filtering, allowing you to treat complex window outputs as static attributes for further processing.