Why CTEs matter in PostgreSQL - See It in Action

Why CTEs Matter in PostgreSQL

📖 Scenario: You are working with a sales database that tracks orders and customers. You want to organize your queries better and make them easier to read and maintain. Common Table Expressions (CTEs) help you do this by letting you define temporary named result sets that you can use within a larger query.

🎯 Goal: Build a PostgreSQL query using a CTE to find customers who placed orders with a total amount greater than 1000. This will help you understand how CTEs make queries clearer and more manageable.

📋 What You'll Learn

Create a table called customers with columns customer_id (integer) and customer_name (text).

Create a table called orders with columns order_id (integer), customer_id (integer), and order_amount (numeric).

Insert specific data into both tables as given.

Write a CTE named customer_totals that calculates the total order amount per customer.

Use the CTE to select customers with total order amounts greater than 1000.

💡 Why This Matters

🌍 Real World

CTEs help database developers write clearer, easier-to-maintain queries by breaking complex logic into named parts.

💼 Career

Understanding CTEs is important for roles involving SQL querying, data analysis, and database management, especially in PostgreSQL environments.

1

Create tables and insert data

Create a table called customers with columns customer_id (integer) and customer_name (text). Then create a table called orders with columns order_id (integer), customer_id (integer), and order_amount (numeric). Insert these exact rows into customers: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into orders: (101, 1, 500), (102, 1, 700), (103, 2, 300), (104, 3, 1200).

PostgreSQL

-- Create customers and orders tables and insert data
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables and INSERT INTO to add the rows exactly as specified.

2

Define the CTE for total order amounts

Write a Common Table Expression (CTE) named customer_totals that calculates the total order_amount for each customer_id from the orders table. Use SUM(order_amount) and group by customer_id.

PostgreSQL

WITH customer_totals AS (
    -- Your code here
)

Need a hint?

Use WITH customer_totals AS (SELECT ... GROUP BY customer_id) to create the CTE.

3

Select customers with total orders over 1000

Using the customer_totals CTE, write a query that selects customer_id and total_amount where total_amount is greater than 1000.

PostgreSQL

WITH customer_totals AS (
    SELECT customer_id, SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > 1000;
-- Your code here

Need a hint?

After the CTE, write a SELECT statement filtering on total_amount > 1000.

4

Join with customers to get customer names

Complete the query by joining customer_totals with the customers table on customer_id to select customer_name and total_amount for customers with total orders over 1000.

PostgreSQL

WITH customer_totals AS (
    SELECT customer_id, SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customers.customer_name, customer_totals.total_amount
FROM customer_totals
JOIN customers ON customer_totals.customer_id = customers.customer_id
WHERE total_amount > 1000;
-- Your code here

Need a hint?

Use a JOIN between customer_totals and customers on customer_id to get the customer names.