Building Readable Queries with CTEs

From Subqueries to CTEs: What and Why?

A Common Table Expression is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it like defining a temporary variable or a helper function at the top of a script. You perform a calculation once, give its result a clear name, and then refer to that name in the rest of your query. This approach fundamentally changes how you structure your logic, shifting from a nested, bottom-up style (subqueries) to a sequential, top-down flow.

Let's start with a visual explanation that compares CTEs to subqueries and introduces the core concepts.

The main advantages highlighted in the video and in practice are:

• Improved Readability: Complex logic is broken into named, logical steps.
• Reusability: The same CTE can be referenced multiple times within the same main query, avoiding code duplication.
• Easier Maintenance: It's much simpler to debug or modify a specific, named block of logic than to untangle a deeply nested subquery.
• Recursive Queries: CTEs can reference themselves, allowing you to solve complex problems like traversing hierarchical data (e.g., an organizational chart). We won't cover recursion today, but it's a powerful capability unique to CTEs.

This image provides a perfect side-by-side comparison, showing how a FROM subquery can be rewritten as a much clearer CTE.

The Basic Structure: Standalone CTEs

A CTE is created using the WITH keyword, followed by the name you want to give your temporary result set.

The basic syntax is:

WITH CteName AS (
    -- This is your inner query, just like a subquery
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
-- This is your outer (main) query
SELECT *
FROM CteName;

Let's see this in action by building our first CTE.

As you saw, the process is straightforward:

1. Write and test your inner query to get the intermediate result you need.
2. Place the WITH CteName AS (...) syntax before it.
3. Write your main query that selects from CteName.

Building on the Foundation: Multiple and Nested CTEs

The real power of CTEs becomes apparent when your analysis involves multiple steps. You can define several CTEs in a sequence and even have later CTEs build upon earlier ones.

1. Multiple Standalone CTEs

If you have several independent, intermediate calculations, you can define them as separate CTEs. You only use the WITH keyword once; subsequent CTEs are separated by a comma.

WITH
FirstCTE AS (
    SELECT ...
),
SecondCTE AS (
    SELECT ...
)
SELECT *
FROM FirstCTE
JOIN SecondCTE ON ...;

Let's watch how to add a second, independent calculation to our previous query.

2. Nested CTEs

A nested CTE is one that refers to a previously defined CTE in the same WITH clause. This allows you to create a logical pipeline, where the output of one step becomes the input for the next. This is where CTEs truly shine in improving structure.

WITH
TotalSales AS (
    -- Step 1: Calculate total sales
    SELECT customer_id, SUM(sales) as total_sales
    FROM orders
    GROUP BY customer_id
),
CustomerRank AS (
    -- Step 2: Rank customers based on the result of the first CTE
    SELECT customer_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) as sales_rank
    FROM TotalSales -- Notice we are querying the first CTE
)
SELECT *
FROM CustomerRank
WHERE sales_rank <= 10;

This next video segment demonstrates this concept perfectly.

A Note on Best Practices

As with any powerful tool, it's possible to overuse CTEs. A query with 20 chained CTEs can become just as confusing as a deeply nested subquery.

The key guideline is to use CTEs to represent distinct, logical steps in your analysis. A good rule of thumb is to keep it between 3-5 CTEs for most queries. If it gets more complex, it might be time to think about breaking the logic into multiple queries or using temporary tables.

Conclusion

You've now learned how to structure complex SQL queries in a clean, readable, and maintainable way using Common Table Expressions. This is a significant step up from writing complex subqueries and is a technique you will use daily as a data analyst.

Key Takeaways:

• CTEs are temporary, named result sets defined with the WITH clause, acting like virtual tables for the duration of a query.
• They promote a top-down, sequential logic that is easier to read and debug than the nested logic of subqueries.
• You can define multiple CTEs in a single statement by separating them with a comma.
• Nested CTEs allow you to create a data-processing pipeline by having one CTE query a previous one.
• CTEs enable reusability, allowing you to reference the same intermediate result set multiple times in your main query.

In our next lesson, we will explore CASE statements. You'll see how CASE statements are often used inside CTEs to create new categorical columns or apply conditional logic as part of your data preparation pipeline, adding another layer of sophistication to your queries.