Welcome to the next lesson in our "Advanced Analytical SQL" module. In our previous session, you learned how to use CASE statements to inject conditional logic into your queries, creating custom categories on the fly. As you saw, combining CASE with GROUP BY allows for powerful, customized aggregations. However, as the logic inside your queries grows, they can quickly become long, nested, and difficult to read—a challenge you've likely encountered when debugging complex logic in PHP.
This lesson introduces a powerful tool for managing that complexity: Common Table Expressions (CTEs). CTEs allow you to break down a complex query into logical, sequential, and readable steps. Think of them as creating temporary, named result sets that you define once and can then reference in your main query, much like defining a helper function or a variable in a programming language. Mastering CTEs is a significant step toward writing the clean, maintainable, and sophisticated SQL expected of a senior data analyst.
Our goal is to learn how to write CTEs to structure complex analytical queries, making them easier to build, debug, and understand.
When and Why to Use a CTE
At a high level, the choice between using a CTE and a simpler construct like a subquery often comes down to the complexity of your logic. Subqueries are fine for simple, one-off calculations, but as your analytical needs grow, CTEs become indispensable.
This flowchart provides a great mental model for making this decision.
Essentially, a CTE, defined with the WITH keyword, is a temporary, named result set that exists only for the duration of a single query. It helps you untangle nested logic and lay it out in a clear, top-to-bottom fashion.
The Anatomy of a CTE
The syntax for a CTE is straightforward. You use the WITH keyword to name your temporary result set, define it with a SELECT statement, and then refer to it by name in your subsequent query.
Let's formalize this by looking at a quick reference.
Simplify complex queries with Common Table Expressions ...
This article from the Metabase Learn series provides a concise introduction to CTEs and their benefits.
Focus on the introduction and the CTE syntax section. This will solidify your understanding of the WITH ... AS structure.
From Tangled Subqueries to Clean CTEs
The best way to appreciate the power of CTEs is to see how they transform a messy query into a clean one. The following video walks through a common analytical scenario: finding stores whose sales are above the overall average. It first solves the problem with confusing, nested subqueries and then refactors it using CTEs, demonstrating the dramatic improvement in clarity.
SQL WITH Clause | How to write SQL Queries using WITH Clause | SQL CTE (Common Table Expression)
This video from techTFQ provides a step-by-step demonstration of building a complex query, first with subqueries and then with CTEs.
Please watch the following segments in order: First, get a quick refresher on the basic syntax and a simple example. Next, understand the more complex problem being solved: analyzing sales data across multiple stores. Then, watch the demonstration of solving it with nested subqueries. Pay close attention to how the logic becomes difficult to follow and how the same subquery is repeated. Finally, see how the same problem is solved cleanly using CTEs. Notice how each logical step is separated into its own CTE, making the final query simple and easy to read.
As the video demonstrates, CTEs offer two immediate advantages:
- Readability: The query logic flows from top to bottom. Each
WITHclause defines a clear, logical unit, which you can then compose in the finalSELECTstatement. - Reusability: You define a CTE once and can reference it multiple times in your query, avoiding the repetition and potential performance drag of re-executing the same subquery.
The Power of Chaining and Multi-Level Aggregation
CTEs are not just for cleaning up subqueries; they enable more advanced analytical patterns that are difficult or impossible to express otherwise. Two key patterns are chaining multiple CTEs and performing multi-level aggregations.
Chaining Multiple CTEs
You can define several CTEs in a sequence, separated by commas. A later CTE can even reference a previous one, allowing you to build up your logic step-by-step.
Simplify complex queries with Common Table Expressions ...
This section from the Metabase article shows the simple syntax for defining multiple CTEs.
Read the short section on using multiple CTEs. Notice how only the first CTE is preceded by WITH, and the rest are separated by commas.
Multi-Level Aggregation
A very common task in data analysis is to perform an aggregation on top of another aggregation. For example: "What is the average number of orders per week?" To answer this, you first need to count the orders per week, and then calculate the average of those counts. CTEs make this two-step process clean and explicit.
Simplify complex queries with Common Table Expressions ...
This section demonstrates the classic multi-level aggregation pattern.
Read the section on CTEs for multi-level aggregations. Follow the logic: the CTE first calculates orders_per_week, and the main query then calculates the AVG of the order_count produced by the CTE.
Your Turn: Hands-On with Metabase
Let's apply this to answer a business question using the Metabase sample database.
Question: "Identify all products that were part of an order with a total greater than the average order total for that product's category."
This requires a multi-step process:
- First, we need to calculate the average order total for each product category.
- Then, we need to join this information back to our
OrdersandProductstables to find orders that exceed their category's average.
Let's build this query step-by-step in the Metabase SQL editor.
Step 1: Define the CTE to calculate category averages.
We'll create a CTE named category_avg that finds the average total for each product category. This requires joining Orders and Products.
WITH category_avg AS (
SELECT
p.category,
AVG(o.total) AS avg_category_total
FROM orders AS o
JOIN products AS p
ON o.product_id = p.id
GROUP BY
p.category
)
Step 2: Write the main query to find the products.
Now, we can treat category_avg as a real table. We'll join Orders, Products, and our new category_avg CTE to compare each order's total to the calculated average for its category.
-- The full query including the CTE from above
WITH category_avg AS (
SELECT
p.category,
AVG(o.total) AS avg_category_total
FROM orders AS o
JOIN products AS p
ON o.product_id = p.id
GROUP BY
p.category
)
SELECT
o.id AS order_id,
p.title AS product_title,
p.category,
o.total AS order_total,
ca.avg_category_total
FROM orders AS o
JOIN products AS p
ON o.product_id = p.id
JOIN category_avg AS ca
ON p.category = ca.category
WHERE
o.total > ca.avg_category_total
ORDER BY
p.category,
o.total DESC;
Run this full query in Metabase. The result is a list of "high-value" orders, defined not by a fixed number, but relative to the average for their specific category. You've successfully used a CTE to encapsulate a complex intermediate calculation (category_avg) and then used it in a final, clean query.
Best Practices for Your Team
As a team lead, encouraging clean and readable code is paramount. CTEs are a huge part of this in the SQL world. Here are some best practices to keep in mind.
What are SQL CTE Best Practices? | LearnSQL.com
This article from LearnSQL.com champions the use of CTEs for writing professional, maintainable SQL.
Read the following three sections to understand the professional standard: Start with the section on readability to see another compelling before-and-after example. Then, read the section on avoiding repetition to reinforce the reusability benefit. Finally, review the best practices for naming. Giving CTEs meaningful names is crucial for maintainability.
Conclusion
In this lesson, you've learned how to use Common Table Expressions to tame complexity in your SQL queries. You now understand that CTEs are not just syntactic sugar; they are a fundamental tool for structuring analytical logic, improving readability, and enabling multi-step calculations that are hallmarks of sophisticated data analysis. By defining logical units of work with the WITH clause, you can build queries that are easier for you and your team to read, debug, and maintain.
Key Takeaways:
- CTEs organize complexity: They break down long queries into logical, named steps.
- Syntax: Queries with CTEs start with the
WITHkeyword. Multiple CTEs are separated by commas. - Readability and Maintainability: CTEs make your code self-documenting by giving descriptive names to intermediate steps.
- Reusability: A CTE can be defined once and referenced multiple times within the same query.
- Advanced Patterns: They are essential for multi-level aggregations (e.g., average of a count) and chaining complex logical operations.
The queries you're writing are becoming much more powerful. You can now categorize data with CASE and structure complex logic with CTEs. In our next lesson, we will build on this foundation to explore window functions. These functions allow you to perform calculations across a set of table rows, such as ranking results, calculating running totals, or finding the previous value in a sequence—all things that are extremely powerful for business analysis.