Common Table Expressions (CTEs) Explained

Congratulations! You've reached **Day 50**. You are exactly halfway through your journey to SQL mastery.

Today, we learn the favorite tool of every senior SQL developer: the **Common Table Expression (CTE)**.

What is a CTE?

A CTE allows you to write a named subquery at the **top** of your query, and then use it like a table in your main `SELECT`.

WITH top_customers AS (

SELECT customer_id, SUM(total) as revenue

FROM orders

GROUP BY customer_id

HAVING SUM(total) > 1000

)

SELECT c.name, tc.revenue

FROM customers c

JOIN top_customers tc ON c.id = tc.customer_id;

1. **Readability**: You read from top-to-bottom, just like a story. Subqueries force you to read from the inside-out.

2. **Reusability**: You can use the same CTE multiple times in the same query (e.g., Joining a CTE to itself).

3. **Focus**: It separates the *calculation* from the *presentation*.

Always give your CTEs very clear names (e.g., `active_sessions`, `revenue_summary`). This makes your code self-documenting.

Take a subquery you wrote recently and rewrite it using the `WITH` keyword as a CTE.

*Day 51: Why CTEs Make You Look Senior.*