Why CTEs are needed in SQL - See It in Action

Understanding Why CTEs Are Needed

📖 Scenario: You are working with a sales database that tracks orders and customers. You want to find customers who have placed more than 2 orders and also want to see their total order count.

🎯 Goal: Build a SQL query using a Common Table Expression (CTE) to first calculate the number of orders per customer, then select only those customers with more than 2 orders.

📋 What You'll Learn

Create a CTE named CustomerOrders that counts orders per customer

Use the CTE to select customers with order count greater than 2

Include customer ID and order count in the final output

💡 Why This Matters

🌍 Real World

CTEs are used in real business reports to simplify complex data queries, making it easier to analyze customer behavior and sales trends.

💼 Career

Knowing how to use CTEs is important for data analysts and database developers to write clear, maintainable, and efficient SQL queries.

1

Create the base table Orders

Create a table called Orders with columns OrderID (integer) and CustomerID (integer). Insert these exact rows: (1, 101), (2, 102), (3, 101), (4, 103), (5, 101), (6, 102).

SQL

-- Create the Orders table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the CTE CustomerOrders

Write a CTE named CustomerOrders that selects CustomerID and counts OrderID as OrderCount from Orders, grouping by CustomerID.

SQL

CREATE TABLE Orders (OrderID INT, CustomerID INT);
INSERT INTO Orders (OrderID, CustomerID) VALUES
(1, 101), (2, 102), (3, 101), (4, 103), (5, 101), (6, 102);

WITH CustomerOrders AS (
    -- Your code here
)

Need a hint?

Use WITH to start the CTE and GROUP BY CustomerID to count orders per customer.

3

Use the CTE to filter customers

After the CTE, write a query that selects CustomerID and OrderCount from CustomerOrders where OrderCount is greater than 2.

SQL

CREATE TABLE Orders (OrderID INT, CustomerID INT);
INSERT INTO Orders (OrderID, CustomerID) VALUES
(1, 101), (2, 102), (3, 101), (4, 103), (5, 101), (6, 102);

WITH CustomerOrders AS (
    SELECT CustomerID, COUNT(OrderID) AS OrderCount
    FROM Orders
    GROUP BY CustomerID
)
SELECT CustomerID, OrderCount
FROM CustomerOrders
WHERE -- Your code here

Need a hint?

Use WHERE OrderCount > 2 to filter customers with more than 2 orders.

4

Complete the query with ordering

Add an ORDER BY OrderCount DESC clause to the query to show customers with the highest order counts first.

SQL

CREATE TABLE Orders (OrderID INT, CustomerID INT);
INSERT INTO Orders (OrderID, CustomerID) VALUES
(1, 101), (2, 102), (3, 101), (4, 103), (5, 101), (6, 102);

WITH CustomerOrders AS (
    SELECT CustomerID, COUNT(OrderID) AS OrderCount
    FROM Orders
    GROUP BY CustomerID
)
SELECT CustomerID, OrderCount
FROM CustomerOrders
WHERE OrderCount > 2
-- Your code here

Need a hint?

Use ORDER BY OrderCount DESC to sort results from highest to lowest.