SQL CTE - Power up your queries!

I’ll admit it: I used to write these horrible nested subqueries that were impossible to read. You know the ones—SELECT statements wrapped in SELECT statements wrapped in more SELECT statements. I’d come back to my own code a week later and have no idea what was happening.

Then I discovered CTEs and everything changed. Think of CTEs as temporary tables that exist only for your query. You give them names, you can chain them together, and suddenly your 200-line monster becomes readable.

The WITH Clause: Your New Best Friend

CTEs start with WITH. You name your temporary table, define what goes in it, then use it like any other table.

WITH customer_sales AS (
    SELECT customer_id, SUM(sales_amount) as total_sales
    FROM sales
    GROUP BY customer_id
)
SELECT * FROM customer_sales

Here’s a real problem I solved last week. I needed to find customers who spent more than average. Without CTEs, I’d need a subquery with a GROUP BY nested in the WHERE clause. Ugly.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) as total_sales
    FROM sales 
    GROUP BY customer_id
),
average_sales AS (
    SELECT AVG(total_sales) as avg_amount FROM customer_sales
)
SELECT c.customer_id, c.total_sales
FROM customer_sales c, average_sales a
WHERE c.total_sales > a.avg_amount

Clean, right? Each step is named and easy to follow.

Chaining CTEs: Breaking Down Complexity

This is where CTEs become super powerful. You can define multiple CTEs in one WITH clause, and each one can reference the ones before it. It’s like building a pipeline of data transformations.

I had this complex analysis where I needed to find products that outperformed their regional averages. Multiple steps, but CTEs made it readable:

WITH regional_averages AS (
    SELECT region, AVG(amount) as avg_sales
    FROM sales
    GROUP BY region
),
product_totals AS (
    SELECT product, region, SUM(amount) as total_sales
    FROM sales
    GROUP BY product, region
)
SELECT p.product, p.total_sales, r.avg_sales
FROM product_totals p
JOIN regional_averages r ON p.region = r.region
WHERE p.total_sales > r.avg_sales

Each CTE does one thing clearly. No nested mess.

Recursive CTEs: The Advanced Stuff

Recursive CTEs are wild—they reference themselves. They’re perfect for hierarchical data like org charts or category trees.

A recursive CTE has two parts: the anchor (starting point) and the recursive part (keeps going until it runs out).

Here’s how to trace someone’s management chain:

WITH RECURSIVE management_chain AS (
    -- Start with the employee
    SELECT employee_id, manager_id, employee_name, 1 as level
    FROM employees 
    WHERE employee_name = 'Alice'
    
    UNION ALL
    
    -- Find their manager, then their manager's manager, etc.
    SELECT e.employee_id, e.manager_id, e.employee_name, level + 1
    FROM employees e
    JOIN management_chain m ON e.employee_id = m.manager_id
)
SELECT * FROM management_chain

This keeps climbing the org chart until it hits someone with no manager (the big boss).

Honestly, CTEs made me a better SQL developer. My code went from unreadable nested messes to clear, step-by-step logic. I can actually read my own queries six months later.

Start simple: replace one nested subquery with a CTE. Then try chaining two CTEs together. Once you’re comfortable, you’ll wonder how you ever lived without them.

The recursive stuff is advanced—I probably use it once or twice a year. But the basic CTE patterns? I use those daily. They’re fundamental tools for anyone doing serious data work.