SQL
Structure Complex Queries with Non-Recursive CTEs
Enhance SQL query readability and modularity by breaking down complex logic into manageable, named steps using non-recursive Common Table Expressions.
-- Scenario: Find users who made more than 5 orders in the last 30 days
-- and their average order value.
WITH RecentOrders AS (
SELECT
user_id,
order_id,
order_total
FROM
orders
WHERE
order_date >= NOW() - INTERVAL '30 days'
),
UserOrderSummary AS (
SELECT
user_id,
COUNT(order_id) AS total_recent_orders,
AVG(order_total) AS avg_order_value
FROM
RecentOrders
GROUP BY
user_id
HAVING
COUNT(order_id) > 5
)
SELECT
u.username,
uos.total_recent_orders,
uos.avg_order_value
FROM
users u
JOIN
UserOrderSummary uos ON u.id = uos.user_id
ORDER BY
uos.total_recent_orders DESC;
How it works: This snippet demonstrates the power of non-recursive Common Table Expressions (CTEs) to organize complex SQL queries into logical, readable steps. Instead of nesting multiple subqueries, `RecentOrders` first filters orders from the last 30 days. Then, `UserOrderSummary` aggregates these recent orders to count total orders and calculate average order values per user, filtering for users with more than 5 orders. Finally, the main query joins this summary with the `users` table to display the results. CTEs significantly improve query maintainability and understanding by naming intermediate result sets.