Welcome to the next step in your SQL journey! In our last few lessons, you've learned how to retrieve data, group and summarize it, and even combine information from multiple tables using JOIN clauses. These are the fundamental building blocks of data analysis.
However, real-world data transformations are rarely a single step. They are often a sequence of logical operations: you might filter a dataset, aggregate it, join the result to another table, and then aggregate it again. Trying to write this as one giant query with nested subqueries can quickly become confusing and difficult to maintain.
This lesson introduces a powerful tool for taming that complexity: the Common Table Expression (CTE). You will learn how to use the WITH clause to structure a multi-step SQL transformation into a series of clean, logical, and readable steps. Think of it as creating a recipe, where each CTE is a clear instruction that builds upon the last, leading to your final, desired result.
What is a CTE and Why Use One?
At its heart, a Common Table Expression is a temporary, named result set that exists only for the duration of a single query. It allows you to perform a calculation or transformation, give its result a name, and then refer to that name in subsequent parts of your query as if it were a regular table.
This has two major benefits:
- Readability: It breaks down a complex query into logical chunks, making it read more like a story than a tangled mess of code.
- Modularity: You can define a piece of logic once and reuse it multiple times within the same query.
The following video from Jess Ramos provides an excellent introduction to what CTEs are and their basic syntax.
What are CTEs in SQL in 13 minutes (Common Table Expressions for BEGINNERS)
This video explains what a CTE is and how it helps improve query structure.
Please watch the first few minutes, which cover the definition and a simple example. Pay close attention to the WITH ... AS (...) syntax. What is a CTE? Basic Syntax
As you saw, the basic idea is to name a piece of your query, which makes the whole process much easier to follow.
Use Case 1: Filtering on an Aggregated Value
One of the most common uses for a CTE is when you need to calculate an aggregate (like a SUM or COUNT) and then filter your results based on that calculation. While you can sometimes use the HAVING clause for this, CTEs often make the logic much clearer, especially as queries grow.
Let's see a practical example of this.
What are CTEs in SQL in 13 minutes (Common Table Expressions for BEGINNERS)
This part of the video demonstrates how a CTE can be used to calculate a total quantity per customer, and then the main query filters for customers who ordered more than 100 items. The presenter also compares this to using HAVING, which is a useful contrast.
Watch the section from this example of filtering an aggregate.
Use Case 2: When CTEs are Essential (Nested Aggregations)
Sometimes, you'll face problems that are impossible to solve in a single step in SQL. A classic example is needing to perform an aggregation on top of another aggregation, like finding the average of a SUM. SQL syntax forbids nesting aggregate functions like AVG(SUM(column)).
This is where CTEs move from being "nice to have" for readability to being absolutely essential. You use a CTE to perform the first aggregation, and then your main query performs the second aggregation on the CTE's result.
This is a critical pattern for data analysts and engineers.
What are CTEs in SQL in 13 minutes (Common Table Expressions for BEGINNERS)
The presenter explains why you can't nest aggregate functions and then demonstrates the clean, two-step solution using a CTE. This is a powerful and common use case.
Watch this segment on nested aggregate logic.
Structuring Multi-Step Transformations
Now we get to the heart of today's lesson: using CTEs to build a logical, multi-step pipeline within a single query.
Multiple Independent Steps
Imagine you need to compare each employee's bonus to two different benchmarks: the average bonus for their specific job title, and the average bonus for their geographical region. This requires two separate, independent calculations before you can bring them together. With CTEs, this is straightforward. You define one CTE for each benchmark.
Let's read about the syntax and see an example.
SQL CTEs Explained with Examples | LearnSQL.com
This article from LearnSQL.com provides clear, text-based examples. We'll focus on how to calculate an intermediate result and then how to define multiple intermediate results in one query.
First, read the section CTEs in Action to see how a single CTE is used to calculate an average and join it back. Next, read the section on Multiple CTEs in One Query. Notice how a single WITH keyword is used, and the CTE definitions are separated by a comma.
The pattern is:
WITH
cte_1 AS (
-- First calculation
),
cte_2 AS (
-- Second, independent calculation
)
SELECT ...
FROM original_table
JOIN cte_1 ON ...
JOIN cte_2 ON ...;
Chained or "Nested" Steps
Even more powerfully, one CTE can refer to a previous CTE within the same WITH clause. This allows you to build a sequence of transformations, where each step processes the output of the one before it.
Let's continue with the same article to see how this works.
SQL CTEs Explained with Examples | LearnSQL.com
This section shows how to build a chain of logic where one CTE depends on another.
Read the section titled Nested CTEs. In this example, the first CTE calculates an average per outlet, and the subsequent CTEs use that result to find the minimum and maximum of those averages.
This chained approach is fundamental to data transformation. You're creating a small, logical data pipeline right inside your query.
From Mess to Clarity: A Complete Example
To truly appreciate the power of CTEs for structuring complex queries, it helps to see what the alternative looks like. The following video from techTFQ presents a moderately complex business problem: finding stores whose sales are better than the average sales across all stores.
He first solves it using messy, nested subqueries and then refactors the same logic into a clean, readable query using CTEs.
SQL WITH Clause | How to write SQL Queries using WITH Clause | SQL CTE (Common Table Expression)
This video does a fantastic job of showing the "before" and "after" of using CTEs.
You don't need to watch him build the initial complex query, but I want you to see the transformation. Watch the segment from this side-by-side comparison, where he turns the hard-to-read subquery version into an elegant CTE-based solution.
The final query is not only easier to read but also easier to debug and modify, which are crucial skills for a data engineer.
CTEs vs. Temporary Tables
As a final point, it's useful to know how CTEs relate to another concept you might encounter: temporary tables. While they serve similar purposes, they have a key difference in scope.
How to Use CTEs in BigQuery for Efficient Queries
This article from OWOX BI provides a succinct comparison.
Focus on the table that summarizes the differences. You can find it by searching for the heading "Differences Between CTEs and Temporary Tables at a glance." Read through the comparison table. The key takeaway is that a CTE's life is limited to a single query, while a temporary table can live for an entire session.
For the vast majority of transformations that happen within a single, self-contained analysis, CTEs are the preferred tool.
Conclusion
You've now learned how to move beyond simple, one-shot queries and start structuring complex analytical logic in a professional, maintainable way.
Here are the key takeaways from this lesson:
- Common Table Expressions (CTEs) are defined with the
WITHclause to create named, temporary result sets within a single query. - Their primary benefit is improving readability and maintainability by breaking complex logic into named steps.
- CTEs are essential for performing multi-level aggregations (e.g.,
AVGof aSUM), which SQL does not allow in a single step. - You can define multiple CTEs in a single
WITHclause, and you can chain them so that later CTEs build upon the results of earlier ones. This allows you to create a logical transformation pipeline.
In our next lesson, we'll address a crucial follow-up question: "My complex query ran, but how do I know the result is correct?" You'll learn fundamental data quality techniques to check your results for duplicates, nulls, and other common issues, ensuring the data you produce is reliable.