CTEs for Complex Queries

From Subquery Spaghetti to Readable Recipes

Let's start with the fundamental question: what is a CTE and why is it better than a complex subquery? At its core, a CTE is a temporary, named result set that exists only for the duration of a single query. You define it using the WITH keyword.

This approach transforms a query from a nested, "bottom-to-top" logic (like a subquery) into a sequential, "top-to-bottom" recipe that is much easier to follow.

As the video shows, both queries produce the same result, but the CTE version is organized more logically:

1. Define the building block first: The WITH clause creates a temporary table mp containing the max price per category.
2. Use the building block: The main query then simply selects from mp as if it were a regular table.

This separation of concerns is a core principle of good coding and is highly valued in professional environments.

The Key Advantage: Reusability

One of the most significant advantages of CTEs over subqueries is reusability. A subquery's result can only be used once in the place it's defined. If you need the same intermediate result in another part of your query, you have to write the entire subquery again. This is inefficient and makes the query even harder to read.

A CTE, however, can be referenced multiple times within the same main query.

This ability to define a piece of logic once and reuse it is what makes CTEs so powerful for complex analysis, where you might need to compare a value to an overall average, a segment average, and a total sum—all derived from the same base calculation.

SELECT
    COUNT(DISTINCT campaign_id) AS number_of_high_spend_campaigns,
    (SELECT AVG(daily_spend) FROM (SELECT campaign_id, SUM(spend) AS daily_spend FROM ad_spend GROUP BY campaign_id) AS daily_spends WHERE daily_spend > 1000) AS avg_spend_of_high_performers
FROM 
    (SELECT campaign_id, SUM(spend) AS daily_spend FROM ad_spend GROUP BY campaign_id) AS daily_spends
WHERE 
    daily_spend > 1000;

WITH campaign_daily_spend AS (
    SELECT 
        campaign_id, 
        SUM(spend) AS daily_spend 
    FROM 
        ad_spend 
    GROUP BY 
        campaign_id
)
SELECT 
    COUNT(DISTINCT campaign_id) AS number_of_high_spend_campaigns,
    AVG(daily_spend) AS avg_spend_of_high_performers
FROM 
    campaign_daily_spend
WHERE 
    daily_spend > 1000;

Building a Narrative with Multiple CTEs

The true power of CTEs shines when you need to perform a multi-step analysis. You can define a series of CTEs, where each one builds on the one before it, creating a logical flow that tells the story of your data transformation.

This is like writing a report:

• CTE 1: Gathers the raw data and performs initial cleaning/aggregation.
• CTE 2: Takes the result of CTE 1 and adds customer segmentation.
• CTE 3: Takes the result of CTE 2 and ranks the segments.
• Main Query: Selects the final, polished data for presentation.

This approach is incredibly valuable for a BA because it makes your analytical process transparent and easy for others (and your future self) to understand.

Practical Advice and Broader Context

When to Use a CTE?

CTEs are fantastic, but they are not the only tool for organizing SQL. It's helpful to know where they fit.

In short:

• Subquery/Derived Table: Fine for a simple, one-time use inside a query.
• CTE: The best choice for organizing multi-step logic or when you need to reuse an intermediate result within a single, complex query.
• View: A permanent, stored query. Use this when you have a piece of logic that needs to be reused across many different queries by multiple users.

A Word of Caution: Don't Overdo It

With great power comes great responsibility. It can be tempting to turn every single step of your query into a CTE. This can backfire, making a query unnecessarily long and complex.

Conclusion

Today you've learned one of the most important techniques for writing professional, advanced SQL. By breaking down complex logic into named, sequential steps, CTEs make your queries dramatically more readable, maintainable, and easier to debug.

Key Takeaways:

• A Common Table Expression (CTE) is a temporary result set defined with the WITH keyword to organize complex queries.
• CTEs improve readability by creating a top-to-bottom logical flow, unlike nested subqueries.
• A key advantage of CTEs is reusability: you can reference the same CTE multiple times within your main query.
• You can chain multiple CTEs together, with later CTEs building upon earlier ones, to create a clear, step-by-step analytical narrative.
• While powerful, CTEs should be used judiciously to maintain clarity. Aim for a manageable number of logical steps.

For a Business Analyst, writing code that is easy for others to understand is just as important as getting the right answer. CTEs are your best tool for achieving that clarity in SQL.

Preview of the Next Lesson:
We've now mastered how to structure and organize complex queries. Next, we will learn about Window Functions. These functions allow you to perform calculations across a set of table rows that are somehow related to the current row. Think of tasks like "ranking products by sales within each category" or "calculating a running total of revenue over time." Window functions are often used inside CTEs to prepare data for final analysis, and together, they form the backbone of advanced analytics in SQL.