Lesson 1: Common Table Expressions (CTEs)

Section 4 — Lesson 1: Common Table Expressions (CTEs)

Welcome to Section 4. You already know how to write solid SELECT queries, joins, grouping,
safe filtering, and practical staging with temp tables.

Now we move into the patterns DBAs use for real operations: troubleshooting, building clean scripts,
and writing queries that are easier to maintain under pressure.
Your first tool in this section is the CTE (Common Table Expression).

Lesson description: You will learn what a CTE is, why DBAs use it,
the correct syntax rules, how to chain multiple CTEs, when a CTE is better than a subquery or temp table,
how to use CTEs for de-duplication and “top per group” logic, and how recursive CTEs work conceptually
(with safe examples). You will also learn common mistakes that cause incorrect results or poor performance.

1) What Is a CTE (In Plain DBA Terms)?

A CTE is a named, temporary result set that exists only for the single statement that follows it.
Think of it as a readable “building block” you can reference like a table—without creating a real table.

DBAs use CTEs primarily to:

• make complex queries easier to read (step-by-step logic)
• avoid repeating the same derived logic multiple times
• cleanly apply filters/aggregations in stages
• build “top N per group” and de-duplication logic
• solve hierarchical problems (recursive CTE)

Important: A CTE is not a stored object. It does not persist. It is only “visible” to the next statement.

2) CTE Syntax Rules (DBA Must-Know)

A CTE is written with WITH and must be followed immediately by a single statement
(SELECT/INSERT/UPDATE/DELETE/MERGE).

Basic syntax

WITH CTE_Name AS
(
    SELECT ...
)
SELECT ...
FROM CTE_Name;

Critical rules

• If the CTE is not the first statement in your batch, put a semicolon before WITH:
  ;WITH ...
• The CTE exists only for the next statement.
  You cannot reference it in a second statement unless you define it again.
• A CTE is not inherently “faster.” It is mainly about clarity and expressing intent.
  Performance depends on the final execution plan.

3) Your First Real DBA CTE (Step-by-Step Filtering)

Scenario: you want to review recent high-value orders in AdventureWorks, but you want your logic to be readable.
A CTE lets you “stage” the first step as a clean dataset, then apply additional filters in the final query.

;WITH RecentOrders AS
(
    SELECT
        soh.SalesOrderID,
        soh.OrderDate,
        soh.CustomerID,
        soh.TotalDue
    FROM Sales.SalesOrderHeader AS soh
    WHERE soh.OrderDate >= DATEADD(DAY, -30, CAST(SYSDATETIME() AS date))
)
SELECT
    ro.SalesOrderID,
    ro.OrderDate,
    ro.CustomerID,
    ro.TotalDue
FROM RecentOrders AS ro
WHERE ro.TotalDue >= 5000.00
ORDER BY ro.TotalDue DESC;

Why DBAs like this: if a report is wrong, you can quickly inspect the CTE definition and confirm the base dataset.

4) Multiple CTEs (Clean “Pipeline” Queries)

One of the strongest uses of CTEs is chaining steps. Each CTE is a stage in your pipeline.
This is extremely readable and reduces bugs.

Example: compute total spend per customer, then select top customers.

;WITH CustomerSpend AS
(
    SELECT
        soh.CustomerID,
        SUM(soh.TotalDue) AS TotalSpent,
        COUNT(*)          AS OrderCount
    FROM Sales.SalesOrderHeader AS soh
    GROUP BY soh.CustomerID
),
TopCustomers AS
(
    SELECT
        cs.CustomerID,
        cs.TotalSpent,
        cs.OrderCount
    FROM CustomerSpend AS cs
    WHERE cs.TotalSpent >= 50000.00
)
SELECT TOP (25)
    tc.CustomerID,
    tc.OrderCount,
    tc.TotalSpent
FROM TopCustomers AS tc
ORDER BY tc.TotalSpent DESC;

DBA habit: name your CTEs by what they represent (CustomerSpend, TopCustomers), not vague names like CTE1.

5) CTE vs Subquery vs Temp Table (How a DBA Chooses)

Use a CTE when:

• you need readable staged logic for a single statement
• you want to avoid repeating derived logic in the same statement
• the dataset is not reused across multiple statements

Use a subquery when:

• the derived logic is small and only used once
• adding a CTE would reduce readability (yes, that can happen)

Use a temp table when:

• you need to reuse the staged data across multiple statements
• you need indexing control for performance
• you are writing an operational script with multiple phases (stage → validate → act → report)

Job-ready takeaway: CTEs are best for clarity inside a single statement.
Temp tables are best for multi-step scripts and performance tuning control.

6) De-Duplication (ROW_NUMBER Preview) Using a CTE

DBAs frequently need to de-duplicate results. The cleanest pattern is:

1. create a CTE that assigns a row number per group
2. filter to the rows you want (often row_number = 1)

You will learn window functions next lesson; for now, focus on the CTE structure.

;WITH RankedOrders AS
(
    SELECT
        soh.CustomerID,
        soh.SalesOrderID,
        soh.OrderDate,
        soh.TotalDue,
        ROW_NUMBER() OVER (PARTITION BY soh.CustomerID ORDER BY soh.OrderDate DESC) AS rn
    FROM Sales.SalesOrderHeader AS soh
)
SELECT
    ro.CustomerID,
    ro.SalesOrderID,
    ro.OrderDate,
    ro.TotalDue
FROM RankedOrders AS ro
WHERE ro.rn = 1
ORDER BY ro.OrderDate DESC;

What this does: returns the most recent order per customer.
This is an extremely common reporting requirement.

7) Recursive CTEs (Hierarchies) — What DBAs Should Know

A recursive CTE is used for hierarchical relationships:
employee → manager, category → parent category, organizational trees.

In many DBA environments, recursion is used carefully because large hierarchies can be expensive.
You should still understand the pattern because you will see it in real systems.

7A) The recursive CTE shape (anchor + recursive member)

This is a safe template. You must have:

• Anchor query (starting rows)
• Recursive query (joins back to the CTE)

/*
Template:
;WITH CTE AS
(
    -- Anchor member
    SELECT ...

    UNION ALL

    -- Recursive member
    SELECT ...
    FROM SomeTable t
    JOIN CTE ON ...
)
SELECT * FROM CTE;
*/

DBA safety note: SQL Server has a recursion limit (default 100).
You can control it with OPTION (MAXRECURSION n).

8) Common CTE Mistakes (And How to Avoid Them)

Mistake A: Forgetting the semicolon before WITH

If the previous statement didn’t end with a semicolon, SQL Server can throw a syntax error.
Fix by using ;WITH.

Mistake B: Trying to reuse a CTE in a second statement

CTE scope is only the next statement. If you need reuse across multiple statements, use a temp table.

Mistake C: Writing a CTE that hides row multiplication

A CTE can make queries readable, but it can also hide mistakes if you don’t validate row counts.
Always confirm output grain.

Mistake D: Assuming a CTE is “materialized”

SQL Server may inline CTE logic into the final query. Do not assume the CTE is a separate stored result.
Always verify with the execution plan (later section).

9) Practice (Job-Ready Exercises)

Do these in AdventureWorks. Save each script in your “DBA script pack.”

1. Create a CTE named RecentOrders that returns the last 14 days of orders, then filter to orders over $2,000.
2. Create a two-step CTE pipeline: CustomerSpend (sum TotalDue by CustomerID), then TopCustomers
   (filter to customers spending over a threshold). Return top 20.
3. Create a CTE that returns one row per customer: their most recent order date (use ROW_NUMBER as shown).
4. Validate your output grain for each query: one row per order? per customer? Confirm with COUNT checks.

Lesson Summary

• A CTE is a named temporary result set used for readability and staged logic in a single statement.
• CTEs are excellent for pipelines, de-duplication patterns, and “top per group” logic.
• CTEs do not persist; use temp tables for multi-step scripts or performance/index control.
• DBAs validate output grain and row counts to ensure CTEs don’t hide mistakes.
• Recursive CTEs solve hierarchies, but DBAs use them carefully and control recursion depth.

Next Lesson: Window Functions (ROW_NUMBER, RANK, SUM OVER)

Next, you will learn window functions—one of the most valuable skills for reporting, troubleshooting,
and “top per group” logic. Window functions are a major reason CTEs become so powerful in DBA scripts.