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.

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;

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

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);

Database Overview & Sample Data

The database is pre-loaded with the Customers and Orders tables. Run the queries below to explore the data.

Customers Table

SELECT * FROM Customers;

Orders Table

SELECT * FROM Orders;

Example: Finding "High Value" Customers

Without CTE (Using Subquery):

SELECT c.Name, SUM(o.Amount) AS TotalSpent
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;

Result:

Recursive CTEs

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 generating sequences.

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: