Mastering CTEs: Writing Cleaner, Better SQL

If you've ever looked at a SQL query with three levels of nested subqueries and felt a headache coming on, you're not alone.

Enter Common Table Expressions (CTEs).

Introduced in the WITH clause, CTEs allow you to define temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of them as variables for your queries.

CTE vs. Subquery: The Readability Test

Let's look at a classic problem: Finding departments where the average salary is higher than the company-wide average.

The Subquery Way (Hard to read)

SELECT department, AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);

You have to read the code "inside out" to understand it.

The CTE Way (Easy to read)

With CTEs, you read from top to bottom. First, we calculate the company average. Then, we calculate department averages. Finally, we compare them. It tells a story.

[!TIP] Messy SQL? CTEs help readability, but formatting matters too. Use our SQL Formatter to instantly beautify your complex queries.

Anatomy of a CTE

WITH CteName AS (
    -- Your query here
    SELECT column1, column2 FROM table
)
SELECT * FROM CteName;

You can define multiple CTEs by separating them with commas:

CTEs vs Subqueries vs Views vs Temporary Tables

CTEs are not the answer to every query-structuring problem. They sit in the middle of a broader toolkit.

Use a CTE when:

• You want to break one query into readable steps
• You need to reference an intermediate result more than once in the same statement
• You need recursion

Use a subquery when:

• The logic is short and only used once
• Introducing a named step would make the query feel heavier, not clearer

Use a view when:

• The logic should be reused across many reports or application queries
• You want to expose a stable business-facing dataset

Use a temporary table when:

• You need to persist intermediate data across multiple statements
• You want to inspect or index the intermediate result separately
• The transformation pipeline is too large or expensive for one statement

The practical rule is simple: use a CTE for query structure, not as a default replacement for every other SQL construct.

Recursive CTEs: The Superpower

This is where CTEs do things standard subqueries simply can't. A Recursive CTE can reference itself. This is incredibly useful for hierarchical data (like org charts) or generating sequences.

Example 1: Generating a Sequence

Example 2: Traversing an Org Chart

Imagine a table where every employee has a manager_id. How do you find the entire chain of command for a specific employee?

How it works:

1. Base Case: Finds the root node (Big Boss).
2. Recursive Step: Finds everyone whose manager_id matches the id found in the previous step.
3. Repeat: Keeps going until no more matches are found.

Real-World Places CTEs Shine

CTEs are especially useful when a business question naturally breaks into stages.

Examples:

• Funnels: define one CTE per stage, then compare counts
• Top-N per group: rank rows in one CTE, filter in the outer query
• Deduplication: assign ROW_NUMBER() in a CTE, then keep only the first row
• Cohort analysis: define the first activity date in one step, retention logic in the next
• Hierarchies: use recursive CTEs to walk org charts, category trees, or referral graphs

This is why CTEs show up constantly in analytics engineering and interview questions. They make complex transformations readable without forcing you to create permanent schema objects.

Performance: Do CTEs Make Queries Faster?

Not by themselves.

A CTE improves clarity first. Performance depends on the database engine and the final execution plan, not on the fact that the query uses WITH.

Important caveats:

• Some databases may inline a CTE into the main query
• Some engines may materialize it as an intermediate result
• Reusing a heavy CTE multiple times can help in some cases and hurt in others

So the right workflow is:

1. Write the clearest version first
2. Run EXPLAIN or EXPLAIN QUERY PLAN
3. Optimize only if the plan shows a real bottleneck

Do not assume "CTE = slower" or "CTE = faster". Assume "CTE = easier to reason about", then verify performance.

Common Mistakes with CTEs

1. Making every query a staircase

CTEs are great until every two-line filter becomes its own named block. Overusing them can make a simple query feel ceremonial.

2. Reusing vague names

Names like cte1, temp, or data add no meaning. Prefer names that describe business purpose, such as HighValueCustomers or RecentOrders.

3. Hiding bad joins

CTEs can make a query look clean while still doing expensive or incorrect work underneath. Readability is not correctness.

4. Forgetting recursion safety

Recursive CTEs need a clear stopping condition. Without one, you can create runaway recursion or duplicate-heavy results.

A Practical Pattern: Rank Then Filter

One of the most common production patterns is "compute a ranking in one step, filter in the next."

WITH RankedOrders AS (
    SELECT
        customer_id,
        order_id,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
)
SELECT customer_id, order_id, created_at
FROM RankedOrders
WHERE rn = 1;

This pattern is easier to read than trying to nest the window function logic directly into a larger query. It also makes debugging much easier because you can inspect the ranked rows first.

When to Use CTEs

1. Readability: When a query is complex and needs to be broken down into logical steps.
2. Reuse: When you need to reference the same sub-result multiple times in the main query.
3. Recursion: When dealing with hierarchical data (trees, graphs) or generating series.

Practice Challenge

Try modifying the Org Chart example above to:

1. Find only the direct reports of 'VP Sales'.
2. Display the hierarchy path like "Big Boss > VP Eng > Lead Dev".

Test Your Skills with a Real Interview Question

Ready to apply CTEs in a production scenario? Try this Salesforce interview question:

Lead Conversion Funnel (Salesforce) - Calculate conversion rates at each stage of a sales funnel using CTEs to organize multi-stage calculations. This question tests your ability to use CTEs with window functions (LAG) to calculate stage-to-stage conversion rates—a critical skill for funnel analysis.

Related Articles

• Understanding SQL Views: Your Virtual Tables Explained for when reusable query logic should become a shared database object.
• Understanding Window Functions for the ranking and analytics patterns often paired with CTEs.
• Mastering Recursive CTEs for the self-referencing patterns that extend the basic WITH model into hierarchies and traversal.
• Understanding Database Indexes: The Key to Performance for the execution side once your CTE-based query is logically correct.

CTEs are a hallmark of intermediate-to-advanced SQL. Mastering them will make your code cleaner, easier to debug, and far more powerful.