Make Your SQL Cleaner with Common Table Expressions (CTEs)

When SQL queries get long and convoluted, it’s easy to lose track of logic. CTEs (Common Table Expressions) help by giving temporary names to subqueries. This lets you write modular, clear, and maintainable SQL. Here are practical examples you can apply today.

Examples

1. Aggregating Sales Data

WITH customer_sales AS (
  SELECT customer_id, SUM(price * quantity) AS total_sales
  FROM orders
  JOIN order_items USING(order_id)
  GROUP BY customer_id
)
SELECT c.customer_name, cs.total_sales
FROM customers c
JOIN customer_sales cs ON c.customer_id = cs.customer_id;

2. Running Monthly Totals

WITH monthly AS (
  SELECT customer_id, YEAR(order_date) AS yr, MONTH(order_date) AS mo, SUM(price * quantity) AS total
  FROM orders
  JOIN order_items USING(order_id)
  GROUP BY customer_id, YEAR(order_date), MONTH(order_date)
),
running AS (
  SELECT *, SUM(total) OVER (PARTITION BY customer_id, yr ORDER BY mo) AS ytd
  FROM monthly
)
SELECT * FROM running;

3. Review Averages by Product Category

WITH ratings AS (
  SELECT product_id, AVG(rating) AS avg_rating
  FROM reviews
  GROUP BY product_id
),
categories AS (
  SELECT product_id, category FROM products
)
SELECT category, AVG(avg_rating)
FROM ratings
JOIN categories USING(product_id)
GROUP BY category;

FAQ

What is a CTE?

A temporary result set you define at the top of a query using WITH.

Why use one?

To organize your SQL into named, readable steps and avoid duplication.

Do CTEs improve performance?

They can, especially when reducing repeated logic or heavy joins.

Is support widespread?

Yes—most modern databases support CTEs, including PostgreSQL, MySQL 8+, SQL Server.

Conclusion

CTEs are a practical solution for cleaning up SQL and breaking logic into manageable parts. If you're working with complex queries, they're worth adding to your toolset. Dive deeper with full examples in the full article Unlocking the Power of CTEs in SQL.