Common Table Expressions (CTEs)

The Readability Problem

My nested subqueries were getting out of hand. A query with three levels of nesting in the `FROM` clause was almost impossible to debug. There had to be a better way.

The Quest: The CTE

A `Common Table Expression (CTE)` allows you to define a temporary result set at the *beginning* of your query using the `WITH` keyword. You then reference it by name in the main `SELECT`.

The Implementation: The WITH Syntax

WITH customer_summary AS (

SELECT customer_id, COUNT(*) AS order_count

FROM orders

GROUP BY customer_id

)

SELECT

cs.customer_id,

cs.order_count,

c.name

FROM customer_summary cs

JOIN customers c ON cs.customer_id = c.id

WHERE cs.order_count > 10;

Why CTEs are Superior

1. **Readability**: The logic is defined at the top, not buried inside parentheses.

2. **Reusability**: You can reference the same CTE multiple times in one query.

3. **Debugging**: You can run the CTE part in isolation to test it.

Multiple CTEs

WITH

sales AS (SELECT ...),

costs AS (SELECT ...)

SELECT sales.total - costs.total AS profit

FROM sales, costs;

The "Oops" Moment

I tried to reference a CTE before I defined it: `WITH a AS (SELECT * FROM b), b AS (...)`. This failed.

**Pro Tip**: CTEs are defined in order. Later CTEs can reference earlier ones, but not the other way around.

The Victory

My 50-line nested query became a clean, 20-line CTE-based query. Code reviews became easier, and bugs became rarer. CTEs are my go-to tool for complex analysis.

Your Task for Today

Rewrite a query that uses a derived table (subquery in FROM) to use a CTE instead. Notice how much cleaner it becomes.

*Day 50: Recursive CTEs—The Mind Bender.*