Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement.

Database Preparation

Run this block to set up the tables used in the examples below.

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers (
    ID INTEGER PRIMARY KEY,
    Name VARCHAR(100)
);

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Amount DECIMAL(10, 2)
);

INSERT INTO Customers (ID, Name)
VALUES (1, 'Alice'), (2, 'Bob');

INSERT INTO Orders (OrderID, CustomerID, Amount)
VALUES (101, 1, 10.00), (102, 1, 10.00), (103, 1, 10.00),
       (104, 1, 10.00), (105, 1, 10.00), (106, 1, 10.00),
       (107, 2, 5.00);

SELECT 'Database Ready' AS Status;

Example: Finding "High Value" Customers

Without CTE (Using Subquery):

SELECT c.Name, SUM(o.Amount)
FROM Customers c
JOIN Orders o ON c.ID = o.CustomerID
WHERE c.ID IN (
    SELECT CustomerID 
    FROM Orders 
    GROUP BY CustomerID 
    HAVING COUNT(*) > 5
)
GROUP BY c.Name;

With CTE:

WITH FrequentShoppers AS (
    -- Step 1: Isolate the list of frequent shopper IDs
    SELECT CustomerID
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(*) > 5
)
SELECT c.Name, SUM(o.Amount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.ID = o.CustomerID
JOIN FrequentShoppers fs ON c.ID = fs.CustomerID -- Join with our CTE
GROUP BY c.Name;

Example: Generating a Number Sequence (1 to 10)

One of the most powerful features of CTEs is the ability to reference themselves. This is called a Recursive CTE and is useful for querying hierarchical data like organizational charts or family trees.

Example: Generating a Number Sequence (1 to 10)

WITH RECURSIVE NumberSequence AS (
    -- Anchor member (Starting point)
    SELECT 1 AS n
    UNION ALL
    -- Recursive member (Loops until condition fails)
    SELECT n + 1
    FROM NumberSequence
    WHERE n < 10
)
SELECT * FROM NumberSequence;

Hands-on Exercise

Task: Create a CTE named OrderStats that calculates the total Amount and the number of orders for each CustomerID. Then, join this CTE with the Customers table to display the Name, TotalAmount, and OrderCount.

Your Query:

-- Write your query here

Expected Results: