CTE referencing another CTE in SQL - Mini Project: Build & Apply

Using CTEs to Reference Another CTE in SQL

📖 Scenario: You are working with a sales database that tracks orders and customers. You want to analyze the total sales per customer and then find customers whose total sales exceed a certain amount.

🎯 Goal: Build a SQL query using two Common Table Expressions (CTEs). The first CTE calculates total sales per customer. The second CTE filters customers with total sales greater than 1000. Finally, select all columns from the second CTE.

📋 What You'll Learn

Create a CTE named CustomerSales that sums order_amount grouped by customer_id from the orders table.

Create a second CTE named HighValueCustomers that selects from CustomerSales only those customers with total sales greater than 1000.

Write a final SELECT statement that retrieves all columns from HighValueCustomers.

💡 Why This Matters

🌍 Real World

CTEs are used in real-world databases to organize complex queries into readable parts, especially when intermediate results are needed for further processing.

💼 Career

Knowing how to write and reference multiple CTEs is a valuable skill for data analysts and database developers to write efficient and maintainable SQL queries.

1

Create the first CTE to calculate total sales per customer

Write a SQL query that starts with WITH CustomerSales AS ( and inside it, select customer_id and the sum of order_amount as total_sales from the orders table grouped by customer_id. Close the CTE with a closing parenthesis.

SQL

-- Write the first CTE named CustomerSales to sum order_amount by customer_id
WITH CustomerSales AS (
    -- Your code here
)

Need a hint?

Use WITH CustomerSales AS (SELECT customer_id, SUM(order_amount) AS total_sales FROM orders GROUP BY customer_id).

2

Create the second CTE to filter customers with total sales over 1000

Add a second CTE named HighValueCustomers after CustomerSales. Inside it, select all columns from CustomerSales where total_sales is greater than 1000. Close this CTE with a closing parenthesis.

SQL

WITH CustomerSales AS (
    SELECT customer_id, SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
),
HighValueCustomers AS (
    -- Your code here
)

Need a hint?

Use HighValueCustomers AS (SELECT * FROM CustomerSales WHERE total_sales > 1000).

3

Write the final SELECT statement to get all high value customers

After the two CTEs, write a SELECT statement that selects all columns from HighValueCustomers.

SQL

WITH CustomerSales AS (
    SELECT customer_id, SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
),
HighValueCustomers AS (
    SELECT * FROM CustomerSales WHERE total_sales > 1000
)
-- Your code here

Need a hint?

Use SELECT * FROM HighValueCustomers to get the filtered customers.

4

Complete the query with proper formatting

Ensure the full query includes both CTEs and the final SELECT statement exactly as follows: WITH CustomerSales AS (...), HighValueCustomers AS (...) SELECT * FROM HighValueCustomers.

SQL

WITH CustomerSales AS (
    SELECT customer_id, SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
),
HighValueCustomers AS (
    SELECT * FROM CustomerSales WHERE total_sales > 1000
)
SELECT * FROM HighValueCustomers
-- Your code here

Need a hint?

Make sure the query includes both CTEs and the final SELECT statement in correct order.