<aside> 📋 A CTE (WITH clause) is a named temporary result set that exists for the duration of a single query. It makes complex queries more readable.

</aside>

Basic CTE Syntax

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

Non-Recursive CTE — Multiple CTEs

-- Step 1: Total sales per customer
WITH CTE_Total_Sales AS (
    SELECT CustomerID, SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)
-- Step 2: Last order date
, CTE_Last_Order AS (
    SELECT CustomerID, MAX(OrderDate) AS Last_Order
    FROM Sales.Orders
    GROUP BY CustomerID
)
-- Step 3: Rank customers
, CTE_Customer_Rank AS (
    SELECT CustomerID, TotalSales,
           RANK() OVER (ORDER BY TotalSales DESC) AS CustomerRank
    FROM CTE_Total_Sales
)
-- Step 4: Segment customers
, CTE_Segments AS (
    SELECT CustomerID, TotalSales,
           CASE
               WHEN TotalSales > 100 THEN 'High'
               WHEN TotalSales > 80  THEN 'Medium'
               ELSE 'Low'
           END AS Segment
    FROM CTE_Total_Sales
)
-- Main query: Join everything
SELECT
    c.CustomerID, c.FirstName, c.LastName,
    cts.TotalSales, clo.Last_Order,
    ccr.CustomerRank, csg.Segment
FROM Sales.Customers AS c
LEFT JOIN CTE_Total_Sales   AS cts ON cts.CustomerID = c.CustomerID
LEFT JOIN CTE_Last_Order    AS clo ON clo.CustomerID = c.CustomerID
LEFT JOIN CTE_Customer_Rank AS ccr ON ccr.CustomerID = c.CustomerID
LEFT JOIN CTE_Segments      AS csg ON csg.CustomerID = c.CustomerID;

Recursive CTE — Generate a Sequence

-- Generate numbers 1 to 20
WITH Series AS (
    SELECT 1 AS MyNumber          -- Anchor query
    UNION ALL
    SELECT MyNumber + 1           -- Recursive query
    FROM Series
    WHERE MyNumber < 20
)
SELECT * FROM Series;

Recursive CTE — Build Employee Hierarchy

WITH CTE_Hierarchy AS (
    -- Anchor: top-level employees (no manager)
    SELECT EmployeeID, FirstName, ManagerID, 1 AS Level
    FROM Sales.Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: get subordinates
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, Level + 1
    FROM Sales.Employees AS e
    INNER JOIN CTE_Hierarchy AS h ON e.ManagerID = h.EmployeeID
)
SELECT * FROM CTE_Hierarchy;

CTE vs Subquery

• CTE — Reusable within same query, improves readability
• Subquery — Inline, can only be used once at that location
• CTE can be recursive; subqueries cannot
• Performance is generally similar; CTE is preferred for clarity