WITH clause syntax in PostgreSQL - Mini Project: Build & Apply

Using WITH Clause in PostgreSQL Queries

📖 Scenario: You are working with a sales database that tracks orders and customers. You want to organize your query by first selecting customers who have placed orders, then using that result to find their total order amounts.

🎯 Goal: Build a PostgreSQL query using the WITH clause to create a temporary result set of customers with orders, then use it to calculate total order amounts per customer.

📋 What You'll Learn

Create a WITH clause named customer_orders that selects customer_id and order_id from the orders table

Select customer_id and the sum of order_amount as total_amount from customer_orders joined with orders

Group the final results by customer_id

Order the results by total_amount in descending order

💡 Why This Matters

🌍 Real World

WITH clauses help organize complex queries by breaking them into smaller parts, making queries easier to read and maintain.

💼 Career

Database developers and analysts use WITH clauses to write clear and efficient SQL queries for reporting and data analysis.

1

Create the WITH clause named customer_orders

Write a WITH clause named customer_orders that selects customer_id and order_id from the orders table.

PostgreSQL

-- Write the WITH clause named customer_orders selecting customer_id and order_id from orders
-- Your code here

Need a hint?

Use WITH customer_orders AS (SELECT customer_id, order_id FROM orders) to create the temporary result.

2

Select customer_id and sum of order_amount as total_amount

After the WITH clause, write a SELECT statement that selects customer_id and the sum of order_amount as total_amount from customer_orders joined with orders on order_id.

PostgreSQL

WITH customer_orders AS (
    SELECT customer_id, order_id FROM orders
)
-- Write SELECT statement to get customer_id and sum(order_amount) as total_amount
-- Your code here

Need a hint?

Join customer_orders with orders on order_id and sum order_amount.

3

Group the results by customer_id

Add a GROUP BY clause to group the results by customer_id in your SELECT statement.

PostgreSQL

WITH customer_orders AS (
    SELECT customer_id, order_id FROM orders
)
SELECT co.customer_id, SUM(o.order_amount) AS total_amount
FROM customer_orders co
JOIN orders o ON co.order_id = o.order_id
-- Add GROUP BY customer_id
-- Your code here

Need a hint?

Use GROUP BY co.customer_id to group the sums by customer.

4

Order the results by total_amount descending

Add an ORDER BY clause to sort the results by total_amount in descending order.

PostgreSQL

WITH customer_orders AS (
    SELECT customer_id, order_id FROM orders
)
SELECT co.customer_id, SUM(o.order_amount) AS total_amount
FROM customer_orders co
JOIN orders o ON co.order_id = o.order_id
GROUP BY co.customer_id
-- Add ORDER BY total_amount DESC
-- Your code here

Need a hint?

Use ORDER BY total_amount DESC to sort from highest to lowest total.