Why Multiple CTEs in one query in SQL? - Purpose & Use Cases

The Big Idea

What if you could turn a messy, multi-step data task into a clean, easy-to-follow query in seconds?

The Scenario

Imagine you have a big spreadsheet with sales data, customer info, and product details all mixed up. You want to find the top customers by sales, but first, you need to clean and organize the data step-by-step. Doing this by hand means copying and filtering data multiple times across sheets.

The Problem

Manually filtering and organizing data is slow and easy to mess up. You might forget a step or mix up numbers. It's hard to keep track of changes, and if the data updates, you have to redo everything from scratch.

The Solution

Using multiple CTEs (Common Table Expressions) in one query lets you break down complex tasks into clear, manageable steps inside the database. Each CTE acts like a mini-table you can build on, making your query easier to read and maintain.

Before vs After

✗ Before

SELECT * FROM sales WHERE date > '2023-01-01';
-- Then manually join with customers and products in separate steps

✓ After

WITH recent_sales AS (
  SELECT * FROM sales WHERE date > '2023-01-01'
), top_customers AS (
  SELECT customer_id, SUM(amount) AS total FROM recent_sales GROUP BY customer_id
)
SELECT * FROM top_customers;

What It Enables

It enables you to write clear, step-by-step queries that handle complex data tasks all at once, saving time and reducing errors.

Real Life Example

A business analyst can quickly find the top-selling products last quarter by first filtering sales data, then summarizing totals, and finally joining with product info--all in one neat query.

Key Takeaways

Manual data handling is slow and error-prone.

Multiple CTEs let you organize complex queries into simple steps.

This approach makes queries easier to read, maintain, and update.