SQL

SQL Query with Common Table Expressions (CTEs)

Simplify complex SQL queries and improve readability by breaking them into logical, named subqueries using Common Table Expressions (CTEs).

WITH RecentOrders AS (
    SELECT customer_id, order_id, total_amount, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
CustomerOrderSummary AS (
    SELECT customer_id, COUNT(order_id) AS num_recent_orders, SUM(total_amount) AS total_recent_spent
    FROM RecentOrders
    GROUP BY customer_id
)
SELECT c.customer_name, cos.num_recent_orders, cos.total_recent_spent
FROM customers c
JOIN CustomerOrderSummary cos ON c.customer_id = cos.customer_id
WHERE cos.total_recent_spent > 500
ORDER BY cos.total_recent_spent DESC;

How it works: This query uses two CTEs (`RecentOrders` and `CustomerOrderSummary`) to first select recent orders, then summarize them by customer. Finally, it joins with the `customers` table to display the names of customers who have spent more than 500 in the last 30 days, improving query readability and modularity.

SQL Query with Common Table Expressions (CTEs)

Need help integrating this into your project?