CTE vs subquery performance in PostgreSQL - Hands-On Comparison

Comparing CTE and Subquery Performance in PostgreSQL

📖 Scenario: You are working with a sales database that tracks orders and customers. You want to analyze how using Common Table Expressions (CTEs) versus subqueries affects query performance when calculating total sales per customer.

🎯 Goal: Build two SQL queries: one using a CTE and one using a subquery to calculate total sales per customer. Observe and compare their performance.

📋 What You'll Learn

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

Insert sample data into orders with at least 5 rows.

Create a CTE query named cte_total_sales that calculates total sales per customer.

Create a subquery that calculates total sales per customer without using a CTE.

💡 Why This Matters

🌍 Real World

Sales analysts often need to summarize data per customer to understand buying patterns and revenue contributions.

💼 Career

Database developers and analysts must write efficient queries and understand how different SQL constructs affect performance.

1

Create the orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer), customer_id (integer), and amount (numeric). Then insert these exact rows into orders: (1, 101, 50.00), (2, 102, 75.50), (3, 101, 20.00), (4, 103, 100.00), (5, 102, 40.00).

PostgreSQL

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

Write a CTE query to calculate total sales per customer

Write a SQL query using a Common Table Expression (CTE) named cte_total_sales that sums the amount for each customer_id from the orders table. Select customer_id and the total amount as total_sales from the CTE.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.50),
(3, 101, 20.00),
(4, 103, 100.00),
(5, 102, 40.00);

-- Write a CTE named cte_total_sales to sum amount per customer
-- Your code here

Need a hint?

Use WITH cte_total_sales AS (SELECT ...) to define the CTE, then select from it.

3

Write a subquery to calculate total sales per customer

Write a SQL query that selects customer_id and total sales as total_sales by using a subquery inside the FROM clause. The subquery should sum amount grouped by customer_id from the orders table.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.50),
(3, 101, 20.00),
(4, 103, 100.00),
(5, 102, 40.00);

WITH cte_total_sales AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_sales FROM cte_total_sales;

-- Write a query using a subquery in FROM to sum amount per customer
-- Your code here

Need a hint?

Use a subquery inside FROM with an alias, then select from it.

4

Add an ORDER BY clause to both queries to sort results

Add an ORDER BY customer_id clause to the end of both the CTE query and the subquery to sort the results by customer_id in ascending order.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.50),
(3, 101, 20.00),
(4, 103, 100.00),
(5, 102, 40.00);

WITH cte_total_sales AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_sales FROM cte_total_sales;

SELECT sub.customer_id, sub.total_sales
FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
) AS sub;

-- Add ORDER BY customer_id to both queries
-- Your code here

Need a hint?

Use ORDER BY customer_id after the SELECT statements to sort results.