The Problem with Nested Subqueries

I've spent a decade reviewing pull requests, and one of the most common patterns I see that makes my eyes glaze over is the "Pyramid of Doom." This happens when a developer nests subqueries four or five levels deep to perform sequential data transformations. While functionally correct, these queries are a nightmare to debug, impossible to read, and often confuse the query optimizer.

When you're dealing with complex business logic—like calculating a customer's lifetime value while filtering for specific regions and adjusting for seasonal returns—trying to track which alias belongs to which subquery becomes a mental tax you shouldn't have to pay.

Enter the Common Table Expression (CTE)

A Common Table Expression, or WITH clause, allows you to define a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. Think of it as creating a named, virtual table that exists only for the duration of that single query. It transforms your SQL from a nested, inside-out structure into a linear, top-to-bottom narrative.

Real-World Scenario: E-commerce Revenue Analysis

Imagine we need to find the top 5% of customers based on their total spend in 2023, but only for those who have made at least three separate purchases. We also need to compare their spend against the average spend of all customers in their respective country. Doing this with subqueries would be a mess. Here is how I would approach this using CTEs.

-- Calculate total spend and order count per customer
WITH CustomerStats AS (
    SELECT 
        customer_id, 
        country_id, 
        SUM(order_total) AS total_spent, 
        COUNT(order_id) AS order_count
    FROM orders
    WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
    GROUP BY customer_id, country_id
),
-- Filter for the 'loyal' segment (3+ orders)
LoyalCustomers AS (
    SELECT * 
    FROM CustomerStats 
    WHERE order_count >= 3
),
-- Calculate the average spend per country for the loyal segment
CountryAverages AS (
    SELECT 
        country_id, 
        AVG(total_spent) AS avg_country_spend
    FROM LoyalCustomers
    GROUP BY country_id
)
-- Final output: Join everything together to find high-value outliers
SELECT 
    lc.customer_id, 
    lc.total_spent, 
    ca.avg_country_spend, 
    (lc.total_spent - ca.avg_country_spend) AS over_average_amount
FROM LoyalCustomers lc
JOIN CountryAverages ca ON lc.country_id = ca.country_id
WHERE lc.total_spent > (SELECT AVG(total_spent) * 1.5 FROM LoyalCustomers)
ORDER BY lc.total_spent DESC
LIMIT 100;

Why This Approach Wins

The primary advantage here is readability. Each CTE has a descriptive name (CustomerStats, LoyalCustomers), which serves as internal documentation. If a bug appears in the final output, I don't have to unravel five layers of parentheses; I can simply run the code inside the LoyalCustomers CTE to verify the intermediate data.

  • Logical Sequencing: The query reads like a story: first we aggregate, then we filter, then we average, then we compare.
  • Reduced Redundancy: Notice how LoyalCustomers is referenced twice—once to calculate country averages and once in the final SELECT. In a subquery world, you'd likely have to write that filter logic twice or wrap it in yet another layer.
  • Easier Maintenance: If the definition of a "loyal customer" changes from 3 orders to 5, I only change one line in one place.
Pro Tip: Be mindful of performance. In some older versions of PostgreSQL or MySQL, CTEs acted as "optimization fences," meaning the database materialized the CTE in memory rather than folding it into the main query. In modern versions, the optimizer is usually smart enough to handle this, but if you notice a slowdown, check your execution plan.

When to Use CTEs vs. Temporary Tables

I often get asked when to use a CTE versus a #TempTable. The rule of thumb I follow is simple: if you only need the data for one query, use a CTE. If you need to index the intermediate results because you're processing millions of rows across multiple different queries in a stored procedure, go with a temporary table. CTEs are for logic organization; temp tables are for performance tuning and persistence.

By switching to CTEs, you're not just writing code that works; you're writing code that your teammates (and your future self) can actually understand six months from now.