Write Better SQL with Common Table Expressions (CTEs)

If your SQL queries are filled with nested subqueries and repeated logic, Common Table Expressions (CTEs) can help. Using the WITH clause, you can break your query into named, readable steps. Here’s how to use CTEs effectively in real scenarios.

Examples of CTEs in Action

1. Total Sales Per Customer

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;

This keeps your aggregation logic separate and reusable.

2. Monthly and Year-To-Date Sales

WITH monthly_sales AS (
  SELECT customer_id, YEAR(order_date) AS year, MONTH(order_date) AS month, SUM(price * quantity) AS total
  FROM orders
  JOIN order_items USING(order_id)
  GROUP BY customer_id, YEAR(order_date), MONTH(order_date)
),
running_totals AS (
  SELECT *, SUM(total) OVER (PARTITION BY customer_id, year ORDER BY month) AS ytd
  FROM monthly_sales
)
SELECT * FROM running_totals;

Stacking CTEs makes each transformation step transparent.

3. Average Ratings by Category

WITH avg_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 avg_ratings
JOIN categories USING(product_id)
GROUP BY category;

CTEs also help label logic clearly with meaningful names.

FAQ

What is a CTE?

A temporary named subquery defined with WITH, usable within a single query.

How does it help?

It structures logic cleanly and allows reuse without repetition.

Is it better than subqueries?

In many cases, yes—it improves readability and maintainability.

Can I use it anywhere?

Yes, on most modern SQL engines like PostgreSQL, SQL Server, MySQL 8+, and Oracle.

Conclusion

CTEs bring clarity and structure to your SQL. Whether you're analyzing sales or cleaning up joins, they're valuable to writing scalable queries. Learn more examples in the full article Unlocking the Power of CTEs in SQL.