Course contentsShow
← Data Science
Lesson 995 of 2,14525. SQL Subqueries and CTEsFree lesson

CTEs with Aggregations and Window Functions

Combining CTEs with GROUP BY and window functions to perform multi-stage analytical computations.

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.