How to Use Nested WITH Clauses in SQL

Nested WITH clauses in SQL are a powerful tool that allow you to create complex queries that can be difficult to achieve using other methods. By nesting one or more Common Table Expressions (CTEs) inside another CTE, you can create a query that references the results of a previous CTE within a subsequent CTE.

To use nested WITH clauses in SQL, you first need to understand the basics of CTEs. A CTE is a temporary result set that is defined within the scope of a single SQL statement. It allows you to define a subquery and then reference it later in the same query.

Here's an example of a simple CTE that defines a subquery that finds all employees who have been with the company for more than five years:

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
)
SELECT *
FROM long_term_employees;

To nest a CTE, you can define a second CTE that references the first CTE within its definition. Here's an example of a nested CTE that finds all employees who have been with the company for more than five years and who have a salary greater than $50,000:

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
), high_salary_employees AS (
   SELECT employee_id, employee_name
   FROM long_term_employees
   WHERE salary > 50000
)
SELECT *
FROM high_salary_employees;

In this example, the long_term_employees CTE is defined first and the high_salary_employees CTE is defined second, using the results of the first CTE within its definition.

By nesting CTEs in this way, you can build up complex queries that reference multiple subqueries and temporary result sets, making it easier to write and understand complex SQL queries.

Example of 3 times Nested WITH Clauses in SQL

Here is an example of using a 3 times nested WITH clause in SQL:

Suppose you have a table called orders that contains data about customer orders for a company, including the order date, the customer ID, the product ID, and the quantity ordered. You want to create a query that shows the total revenue for each product category, broken down by the month of the order, and further broken down by the customer's country.

Here's how you could use a 3 times nested WITH clause to accomplish this:

WITH recursive revenue_by_month AS (
    SELECT
        product_categories.category_name,
        date_trunc('month', order_date) AS order_month,
        customers.country,
        sum(orders.quantity_ordered * products.price) AS revenue
    FROM orders
    JOIN products ON orders.product_id = products.product_id
    JOIN product_categories ON products.category_id = product_categories.category_id
    JOIN customers ON orders.customer_id = customers.customer_id
    GROUP BY 1, 2, 3
),
revenue_by_country AS (
    SELECT
        revenue_by_month.category_name,
        revenue_by_month.order_month,
        revenue_by_month.country,
        sum(revenue_by_month.revenue) AS total_revenue
    FROM revenue_by_month
    GROUP BY 1, 2, 3
),
revenue_by_category AS (
    SELECT
        revenue_by_country.order_month,
        revenue_by_country.country,
        product_categories.category_name,
        sum(revenue_by_country.total_revenue) AS monthly_total_revenue
    FROM revenue_by_country
    JOIN product_categories ON revenue_by_country.category_name = product_categories.category_name
    GROUP BY 1, 2, 3
)
SELECT
    revenue_by_category.order_month,
    revenue_by_category.country,
    revenue_by_category.category_name,
    revenue_by_category.monthly_total_revenue
FROM revenue_by_category
ORDER BY 1, 2, 3;

In this query, the first part of the nested WITH clause defines a common table expression called revenue_by_month. This CTE calculates the revenue for each product category, broken down by the month of the order and the customer's country.

The second part of the nested WITH clause defines a CTE called revenue_by_country. This CTE aggregates the revenue by product category, month, and country.

The third part of the nested WITH clause defines a CTE called revenue_by_category. This CTE calculates the monthly total revenue for each product category, broken down by the month of the order and the customer's country.

Finally, in the outer query, we select the order month, country, category name, and monthly total revenue from the revenue_by_category CTE and order the results by month, country, and category. This query will return a result set showing the total revenue for each product category, broken down by the month of the order and the customer's country.