CTEs with Aggregations and Window Functions
You'll learn how to combine CTEs with GROUP BY aggregations and window functions to break down complex analytical queries into manageable, logical steps.
The Core Idea
CTEs excel at multi-stage analytical computations where you need to aggregate data first, then perform calculations on those aggregates, or mix aggregated and row-level calculations. Think of it like cooking: first you prepare your ingredients (aggregate in one CTE), then you combine them (use window functions or further aggregations in another CTE or final query).
Why This Combination Is Powerful
When you need to:
- Calculate aggregates, then rank those aggregates
- Compute totals by group, then find each group's percentage of the grand total
- Filter based on window function results applied to aggregated data
CTEs let you separate these concerns cleanly rather than nesting complex subqueries.
Basic Pattern
WITH aggregated_data AS (
SELECT
department,
COUNT(*) as employee_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY department
),
ranked_departments AS (
SELECT
department,
employee_count,
avg_salary,
RANK() OVER (ORDER BY avg_salary DESC) as salary_rank
FROM aggregated_data
)
SELECT * FROM ranked_departments WHERE salary_rank <= 3;
First CTE: aggregate by department. Second CTE: apply window function to rank those aggregated results. Final query: filter the ranked aggregates.
This layered approach mirrors how you'd solve the problem mentally: "First, summarize by group. Then, rank those summaries. Finally, pick the top ones."
Key Takeaway: CTEs transform complex analytical queries into a readable pipeline of transformations—aggregate first, then apply window functions or further analysis to those aggregates, making multi-stage computations manageable and maintainable.