SQL
Simplifying Complex Queries with Non-Recursive CTEs
Enhance readability and manage complexity in SQL queries using Common Table Expressions (CTEs) for multi-step logic, without recursion.
WITH RecentOrders AS (
SELECT
order_id,
customer_id,
order_date
FROM
orders
WHERE
order_date >= '2023-01-01'
),
CustomerOrderCounts AS (
SELECT
customer_id,
COUNT(order_id) AS num_recent_orders
FROM
RecentOrders
GROUP BY
customer_id
)
SELECT
c.customer_name,
coc.num_recent_orders
FROM
customers c
JOIN
CustomerOrderCounts coc ON c.customer_id = coc.customer_id
WHERE
coc.num_recent_orders > 5
ORDER BY
coc.num_recent_orders DESC;
How it works: This query uses Common Table Expressions (CTEs) to break down complex logic into more readable and manageable steps. `RecentOrders` first selects orders within a specific date range. `CustomerOrderCounts` then uses this CTE to count the number of recent orders per customer. Finally, the main query joins with the `customers` table and filters for customers with more than 5 recent orders, demonstrating how CTEs improve query structure and make multi-step logic easier to follow.