Introduction
Complex SQL queries often start simple.
A few joins are added. Then some aggregations. Then a few CASE statements. Then another table is required. Before long, the query can become hundreds of lines long, with nested logic and repeated calculations that are difficult to understand and maintain.
One of the most effective ways to improve the structure of such queries is to use Common Table Expressions (CTEs).
This article explains how to approach the conversion of a large SQL query into a CTE-based query while keeping the original business logic intact.
1. What is a CTE?
CTE stands for Common Table Expression.
A CTE is a temporary named result set that exists only for the duration of a SQL statement.
The basic syntax is:
WITH cte_name AS
(
SELECT
column1,
column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;Instead of putting everything into one large query, we can divide the query into smaller logical sections.
For example, instead of:
Large Query
├── Join Table A
├── Join Table B
├── Calculate something
├── Join Table C
├── Calculate something else
└── Final aggregationwe can structure it as:
CTE 1 → Base Data
CTE 2 → Additional Data
CTE 3 → Calculations
↓
Final QueryThis makes the SQL easier to read and reason about.
2. Why Convert a Large SQL Query into CTEs?
CTEs are useful for several reasons.
Readability
A large query can be divided into logical sections with meaningful names.
Maintainability
If a particular piece of logic needs to be changed, it can often be modified inside one CTE instead of searching through a huge query.
Debugging
Individual CTEs can be tested separately.
Reusability within the query
A CTE can be referenced by subsequent parts of the same SQL statement.
Separation of logic
Different business operations can be separated into different stages.
For example:
Customer Data
↓
Sales Data
↓
Monthly Aggregation
↓
Final KPI Calculationcan become:
customers
sales
monthly_sales
final_result3. Important Point: CTE Conversion Is Not Simply Renaming Tables
One of the most common mistakes when learning CTEs is thinking:
"I have five tables, so I should create five CTEs."
That is not necessarily correct.
A CTE should ideally represent a logical processing step, not simply an underlying table.
For example, this:
WITH customers AS
(
SELECT *
FROM customers_table
)doesn't provide much structural benefit by itself.
Instead, a more meaningful CTE could be:
WITH active_customers AS
(
SELECT
customer_id,
customer_name
FROM customers_table
WHERE status = 'ACTIVE'
)Now the CTE represents a business concept:
Active customers.
The goal should therefore be to identify logical stages of the query.
4. Step 1 — Understand the Existing Query First
Before converting anything into a CTE, understand the original SQL.
Do not start by rewriting immediately.
First identify:
What is the main table?
What tables are being joined?
Which joins are
INNER JOIN?Which joins are
LEFT JOIN?What are the join keys?
Which columns are dimensions?
Which columns are measures?
Where are calculations performed?
Where are filters applied?
Where are aggregations performed?
What does the final result represent?
A useful technique is to draw the query as a data flow.
For example:
Orders
|
+---- Customers
|
+---- Products
|
+---- Payments
|
↓
Aggregation
|
↓
Final ReportThis makes it easier to identify where CTEs should be introduced.
5. Step 2 — Identify Logical Processing Stages
Once the query is understood, divide it into logical sections.
Suppose we have a query that calculates customer sales:
Customer Information
↓
Order Information
↓
Product Information
↓
Calculate Order Value
↓
Aggregate by Customer
↓
Final ResultWe could turn this into:
WITH customer_data AS
(
...
),
order_data AS
(
...
),
order_values AS
(
...
),
customer_sales AS
(
...
)
SELECT ...
FROM customer_sales;The important question is not:
"Which table should become a CTE?"
The better question is:
"Which logical processing step should become a CTE?"
6. Step 3 — Start with the Base Dataset
Usually, the first CTE should represent the main dataset from which the report is built.
For example:
WITH customer_data AS
(
SELECT
customer_id,
customer_name,
country
FROM customers
)This establishes the foundation of the query.
The next CTE can then build on it.
7. Step 4 — Separate Related Data
Suppose customer orders are stored separately:
order_data AS
(
SELECT
order_id,
customer_id,
order_date,
amount
FROM orders
)Now the final query can combine:
customer_data
+
order_datainstead of having all source logic mixed together.
8. Step 5 — Move Complex Calculations into CTEs
This is one of the most useful applications of CTEs.
Suppose the original query contains:
SUM(
CASE
WHEN order_date >= ...
THEN amount
ELSE 0
END
)and this logic appears several times.
Instead of repeating the same calculation, a CTE can prepare the required intermediate data.
For example:
WITH order_data AS
(
SELECT
customer_id,
order_date,
amount,
CASE
WHEN amount > 0
THEN amount
ELSE 0
END AS positive_amount
FROM orders
)The final query can then work with:
SUM(positive_amount)This can make complicated calculations much easier to understand.
9. Step 6 — Use Multiple CTEs When the Logic Has Multiple Stages
CTEs can reference previous CTEs.
For example:
WITH customer_data AS
(
SELECT
customer_id,
customer_name
FROM customers
),
order_data AS
(
SELECT
customer_id,
amount
FROM orders
),
customer_sales AS
(
SELECT
c.customer_id,
c.customer_name,
SUM(o.amount) AS total_sales
FROM customer_data c
JOIN order_data o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.customer_name
)
SELECT *
FROM customer_sales;The data flow becomes:
customers
↓
customer_data
↓
┌──────────────┐
│ │
orders → order_data │
│ │
└──────┬───────┘
↓
customer_sales
↓
final SELECTThis is much easier to follow than putting all the logic into one enormous query.
10. Step 7 — Preserve the Original Join Logic
When converting an existing query, this is extremely important.
If the original query contains:
INNER JOINdo not automatically change it to:
LEFT JOINSimilarly, don't change:
LEFT JOINto:
INNER JOINunless there is a deliberate business or technical reason.
Join type affects the result.
For example:
INNER JOINmeans that matching records are required.
Whereas:
LEFT JOINpreserves records from the left side even when a matching record doesn't exist on the right.
Therefore, when performing a pure CTE refactoring, the safest approach is:
Preserve the original join types and conditions.
11. Step 8 — Preserve Aggregations
The same principle applies to aggregation.
Suppose the original query contains:
SUM(quantity)Do not move the aggregation to another level without understanding the effect.
For example:
SUM(quantity)before a join can produce a very different result from:
SUM(quantity)after a join.
Therefore, during a structural refactoring, keep track of:
SUMCOUNTAVGMINMAXGROUP BYHAVING
and make sure their logical level remains consistent.
12. Step 9 — Be Careful About Join Multiplication
This is one of the most important things to understand when working with complex SQL.
Suppose:
Customer A
|
+--- 2 orders
|
+--- 3 paymentsIf we join both tables directly:
Customer
↓
Orders
↓
Paymentswe may produce:
2 orders × 3 payments = 6 rowsThis can cause:
SUM(order_amount)to become incorrect because the order records have been duplicated.
CTEs can help solve this by aggregating data before joining.
For example:
WITH order_summary AS
(
SELECT
customer_id,
SUM(amount) AS total_orders
FROM orders
GROUP BY customer_id
),
payment_summary AS
(
SELECT
customer_id,
SUM(amount) AS total_payments
FROM payments
GROUP BY customer_id
)
SELECT
c.customer_id,
o.total_orders,
p.total_payments
FROM customers c
LEFT JOIN order_summary o
ON c.customer_id = o.customer_id
LEFT JOIN payment_summary p
ON c.customer_id = p.customer_id;Now both datasets are already at the customer level before they are joined.
This is often much safer.
13. Step 10 — Convert Incrementally
Do not convert a 500-line query in one step.
A better approach is:
Original Query
↓
Understand data flow
↓
Identify first logical block
↓
Create CTE 1
↓
Test
↓
Create CTE 2
↓
Test
↓
Create CTE 3
↓
Test
↓
Final QueryThis makes errors much easier to identify.
14. Example: Before CTE
Consider this query:
SELECT
c.customer_id,
c.customer_name,
SUM(o.amount) AS total_sales
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY
c.customer_id,
c.customer_name;This query is already relatively simple, so a CTE is not necessarily required.
But suppose the order filtering becomes more complicated.
We could separate it:
WITH filtered_orders AS
(
SELECT
customer_id,
amount
FROM orders
WHERE order_date >= '2026-01-01'
)
SELECT
c.customer_id,
c.customer_name,
SUM(o.amount) AS total_sales
FROM customers c
JOIN filtered_orders o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.customer_name;Now the CTE represents:
Orders that satisfy the required date condition.
15. Example: Multiple CTEs
A more realistic query might contain several stages:
WITH filtered_orders AS
(
SELECT
order_id,
customer_id,
order_date,
amount
FROM orders
WHERE order_date >= '2026-01-01'
),
customer_orders AS
(
SELECT
customer_id,
COUNT(order_id) AS order_count,
SUM(amount) AS total_sales
FROM filtered_orders
GROUP BY customer_id
),
customer_data AS
(
SELECT
customer_id,
customer_name
FROM customers
)
SELECT
c.customer_id,
c.customer_name,
o.order_count,
o.total_sales
FROM customer_data c
LEFT JOIN customer_orders o
ON c.customer_id = o.customer_id;Now each CTE has a clear purpose:
filtered_orders
↓
customer_orders
↓
customer_data
↓
final result16. A Practical Method for Converting Any SQL Query
When you receive a large SQL query, use this checklist.
Step 1 — Identify the base table
Ask:
What is the primary dataset?
Create the first CTE around it if appropriate.
Step 2 — Identify supporting datasets
Look at every:
JOINand determine what role that table plays.
Step 3 — Identify repeated logic
Look for:
CASERepeated date calculations, repeated filters, repeated expressions, and repeated aggregations.
These are often candidates for extraction into CTEs.
Step 4 — Identify aggregation levels
Ask:
At what level is this data being aggregated?
Examples:
Order level
Customer level
Product level
Plant + Material level
Month levelThis is critical for avoiding incorrect results.
Step 5 — Create logical CTEs
Name them based on what they represent:
filtered_orders
customer_summary
monthly_sales
product_data
inventory_summaryrather than:
cte1
cte2
cte3Meaningful names make the query self-documenting.
Step 6 — Rebuild the final query
The final SELECT should ideally focus on:
Selecting final columns
Joining prepared datasets
Applying final calculations
Final grouping, if required
The detailed preparation should happen in the CTEs.
17. Naming CTEs Properly
Good:
WITH monthly_sales AS (...)Better than:
WITH cte1 AS (...)Good names should describe what the CTE contains.
Examples:
active_customers
filtered_orders
monthly_revenue
inventory_summary
material_movements
customer_sales
product_inventoryThis makes the SQL easier for another developer to understand.
18. CTEs Are Not Automatically Faster
An important misconception is:
"If I convert my SQL query to CTEs, it will automatically become faster."
Not necessarily.
CTEs primarily improve:
Structure
Readability
Maintainability
Logical organization
Debugging
Performance depends on the database engine and execution plan.
A well-designed CTE query can sometimes improve performance by allowing you to aggregate or filter data earlier, but simply replacing a subquery with a CTE does not guarantee a performance improvement.
Always validate performance using the database's execution plan and appropriate testing.
19. CTE Refactoring vs Query Optimization
These are two different tasks.
CTE Refactoring
Goal:
Make the existing query easier to understand without changing its result.
Original Logic
↓
Better StructureQuery Optimization
Goal:
Improve execution efficiency while maintaining correct results.
This might involve:
Filtering earlier
Aggregating before joins
Reducing unnecessary columns
Removing unnecessary joins
Improving join strategy
Avoiding row multiplication
Reviewing indexes or database-specific optimization techniques
A query can therefore be:
Well structured but slowor:
Fast but difficult to maintainThe ideal solution aims for both correctness and maintainability.
20. How to Validate a CTE Refactoring
When converting an existing query, never assume that the rewritten version is correct just because it looks cleaner.
Compare the original and new queries.
A simple validation approach is:
Original Query
↓
Result A
CTE Query
↓
Result B
Compare A and BCheck:
Row count
Distinct key count
Aggregated values
NULL behavior
Duplicate records
Join behavior
Date ranges
Filters
Calculated fields
For example:
SELECT COUNT(*)
FROM original_result;and:
SELECT COUNT(*)
FROM cte_result;Then compare important business measures.
For analytical queries, this is especially important because a query can return the expected columns while still producing incorrect aggregate values.
21. Common Mistakes When Converting SQL to CTEs
Mistake 1 — Creating a CTE for every table
Not every table needs its own CTE.
Create CTEs around meaningful logical operations.
Mistake 2 — Changing the business logic
A refactoring should not accidentally change:
JOIN type
WHERE conditions
GROUP BY level
Aggregation logic
Date logic
NULL handlingMistake 3 — Ignoring join cardinality
Multiple rows on both sides of a join can multiply records.
Always understand the relationship between datasets before aggregating.
Mistake 4 — Moving aggregation without checking the grain
Moving:
SUM()from the final query into a CTE can change the result if the grouping level changes.
Mistake 5 — Using meaningless names
Avoid:
cte1
cte2
temp
data1Prefer:
monthly_sales
inventory_summary
material_movements
active_customersMistake 6 — Assuming CTE means performance optimization
CTEs primarily provide a cleaner structure. Performance depends on how the database optimizer executes the query.
22. A Reusable CTE Template
For future SQL refactoring tasks, a generic structure can look like this:
WITH base_data AS
(
SELECT
...
FROM source_table
),
filtered_data AS
(
SELECT
...
FROM base_data
WHERE ...
),
calculated_data AS
(
SELECT
...,
CASE
WHEN ...
THEN ...
ELSE ...
END AS calculated_column
FROM filtered_data
),
aggregated_data AS
(
SELECT
key_column,
SUM(measure) AS total_measure
FROM calculated_data
GROUP BY key_column
)
SELECT
...
FROM aggregated_data;The exact structure will depend on the query, but the principle remains the same:
Source
↓
Filter
↓
Transform
↓
Aggregate
↓
Final Result23. The Most Important Principle
When converting a large SQL query into CTEs, don't ask:
"How can I turn this SQL into CTE syntax?"
Instead ask:
"What are the logical stages of this query?"
Once the stages are identified, the CTE syntax becomes straightforward.
A complex query might actually represent something as simple as:
Get the data
↓
Filter the data
↓
Join additional information
↓
Transform the data
↓
Aggregate the data
↓
Generate the final reportEach of those stages can potentially become a CTE.
Conclusion
CTEs are not just a syntax feature. They are a way of organizing SQL logic into understandable processing stages.
A good CTE-based query should allow someone reading it to understand the data flow without having to decode one enormous SELECT statement.
The general approach is:
1. Understand the original query
↓
2. Identify the data flow
↓
3. Identify logical processing stages
↓
4. Create meaningful CTEs
↓
5. Preserve joins and business logic
↓
6. Rebuild the final SELECT
↓
7. Validate the results
↓
8. Optimize separately if requiredThe goal is not simply to use CTEs.
The goal is to make complex SQL easier to understand, maintain, debug, and safely extend — without losing the correctness of the original logic.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.