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

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
)

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

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

Hint

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

Practice

1. What is the main purpose of using a Common Table Expression (CTE) in PostgreSQL?

easy

A. To create indexes automatically

B. To break complex queries into simpler, readable parts

C. To permanently store data in the database

D. To replace all JOIN operations

Solution

1. Step 1: Understand what a CTE does

   A CTE allows you to write a temporary named result set that you can use within a query, making complex queries easier to manage.

2. Step 2: Compare options to CTE purpose

   Options A, B, and D describe other database features, not the main purpose of CTEs.

3. Final Answer:

   To break complex queries into simpler, readable parts -> Option B

4. Quick Check:

   CTEs simplify complex queries = C [OK]

Hint: CTEs simplify complex queries by naming parts [OK]

Common Mistakes:

• Thinking CTEs store data permanently
• Confusing CTEs with indexes
• Assuming CTEs replace JOINs entirely

2. Which of the following is the correct syntax to start a CTE in PostgreSQL?

easy

A. WITH cte_name AS (SELECT * FROM table_name)

B. CREATE CTE cte_name SELECT * FROM table_name

C. DEFINE cte_name AS SELECT * FROM table_name

D. START CTE cte_name (SELECT * FROM table_name)

Solution

1. Step 1: Recall CTE syntax

   In PostgreSQL, a CTE starts with the keyword WITH followed by the CTE name and AS, then the query in parentheses.

2. Step 2: Check each option

   Only WITH cte_name AS (SELECT * FROM table_name) matches the correct syntax. Options A, C, and D use invalid keywords or structure.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) -> Option A

4. Quick Check:

   CTE starts with WITH ... AS (...) = B [OK]

Hint: CTEs always start with WITH keyword [OK]

Common Mistakes:

• Using CREATE instead of WITH
• Omitting AS keyword
• Not enclosing query in parentheses

3. Given the query:

WITH recent_orders AS (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days') SELECT customer_id FROM recent_orders;

What does this query return?

medium

A. An error because CTE is not allowed here

B. All orders from the last 7 days

C. All customers in the database

D. Customer IDs who placed orders in the last 7 days

Solution

1. Step 1: Analyze the CTE definition

   The CTE named recent_orders selects all orders where the order_date is within the last 7 days.

2. Step 2: Analyze the main query

   The main query selects customer_id from recent_orders, so it returns customer IDs who placed orders in the last 7 days.

3. Final Answer:

   Customer IDs who placed orders in the last 7 days -> Option D

4. Quick Check:

   CTE filters recent orders, main query selects customer_id = A [OK]

Hint: CTE defines data, main query selects from it [OK]

Common Mistakes:

• Thinking it returns all orders instead of customer IDs
• Assuming it returns all customers
• Believing CTE causes syntax error here

4. Identify the error in this PostgreSQL query using a CTE:

WITH cte AS SELECT * FROM users SELECT * FROM cte;

medium

A. Missing parentheses around the CTE query

B. CTE name cannot be 'cte'

C. SELECT * FROM cte is invalid syntax

D. WITH keyword should be lowercase

Solution

1. Step 1: Check CTE syntax

   The CTE query must be enclosed in parentheses after AS. The query misses parentheses around SELECT * FROM users.

2. Step 2: Validate other parts

   CTE name 'cte' is valid, SELECT * FROM cte is correct, and WITH keyword case does not matter.

3. Final Answer:

   Missing parentheses around the CTE query -> Option A

4. Quick Check:

   CTE query must be in parentheses = D [OK]

Hint: Always put CTE query inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around CTE query
• Thinking CTE names are restricted
• Confusing case sensitivity of WITH

5. You want to optimize a complex report query by breaking it into steps using CTEs. Which of these is a key advantage of using CTEs for performance in PostgreSQL?

hard

A. CTEs automatically create indexes on intermediate results

B. CTEs always cache intermediate results to speed up queries

C. CTEs allow controlling query execution order and readability

D. CTEs reduce disk space usage by compressing data

Solution

1. Step 1: Understand CTE behavior in PostgreSQL

   CTEs help organize queries into logical steps, improving readability and sometimes controlling execution order.

2. Step 2: Evaluate performance claims

   CTEs do not always cache results (PostgreSQL inlines them unless MATERIALIZED is used), do not create indexes automatically, nor compress data.

3. Final Answer:

   CTEs allow controlling query execution order and readability -> Option C

4. Quick Check:

   CTEs improve readability and control execution = A [OK]

Hint: CTEs improve readability and control execution order [OK]

Common Mistakes:

• Assuming CTEs always cache results
• Thinking CTEs create indexes automatically
• Believing CTEs compress data to save space