SQL CTEs Explained: Recursive Queries, Performance Traps & Real-World Patterns

-- CONTEXT: We have an e-commerce database.
-- GOAL: Find customers who placed more than 3 orders in 2023
--        AND whose total spend exceeded $500, then rank them by spend.

-- Step 1: Define the first CTE — filter orders from 2023 only
WITH orders_2023 AS (
    SELECT
        customer_id,
        order_id,
        order_total
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2023
      AND order_status = 'completed'   -- ignore cancelled orders
),

-- Step 2: Second CTE references the first — aggregate per customer
customer_summary AS (
    SELECT
        customer_id,
        COUNT(order_id)        AS total_orders,
        SUM(order_total)       AS total_spend
    FROM orders_2023           -- referencing the CTE above, not the raw table
    GROUP BY customer_id
),

-- Step 3: Third CTE — apply business rule filters
high_value_customers AS (
    SELECT
        customer_id,
        total_orders,
        total_spend
    FROM customer_summary
    WHERE total_orders > 3
      AND total_spend > 500.00
)

-- OUTER QUERY: Join filtered customers with their profile data and rank
SELECT
    c.customer_id,
    c.full_name,
    c.email,
    hvc.total_orders,
    hvc.total_spend,
    -- RANK assigns the same rank to ties; ROW_NUMBER would not
    RANK() OVER (ORDER BY hvc.total_spend DESC) AS spend_rank
FROM high_value_customers hvc
JOIN customers c ON c.customer_id = hvc.customer_id
ORDER BY spend_rank;

-- CONTEXT: Company org chart stored as adjacency list.
-- employee_id | full_name        | manager_id | department | salary
-- NULL manager_id means this person is the root (CEO).

-- GOAL: Starting from a given employee, traverse DOWN the org chart
--       to find all direct and indirect reports, their depth level,
--       and guard against circular references in dirty data.

WITH RECURSIVE org_hierarchy AS (

    -- ANCHOR MEMBER: Start with the target employee (e.g., the VP of Engineering)
    -- This executes exactly once.
    SELECT
        employee_id,
        full_name,
        manager_id,
        department,
        salary,
        0                          AS depth,          -- root is level 0
        ARRAY[employee_id]         AS visited_ids,    -- track path to detect cycles
        full_name::TEXT            AS reporting_path  -- human-readable trail
    FROM employees
    WHERE employee_id = 42   -- 42 is our VP of Engineering

    UNION ALL

    -- RECURSIVE MEMBER: Find everyone who reports to the previous level.
    -- 'org_hierarchy' here refers to the PREVIOUS iteration's result set.
    SELECT
        e.employee_id,
        e.full_name,
        e.manager_id,
        e.department,
        e.salary,
        oh.depth + 1                                          AS depth,
        oh.visited_ids || e.employee_id                      AS visited_ids,
        oh.reporting_path || ' -> ' || e.full_name           AS reporting_path
    FROM employees e
    -- JOIN the recursive CTE to itself — this is what walks the tree
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    -- CYCLE GUARD: stop if we've already visited this employee_id in this path
    -- Without this, circular data causes infinite recursion until depth limit hit
    WHERE NOT (e.employee_id = ANY(oh.visited_ids))
)

-- OUTER QUERY: Present the full org tree with summary stats
SELECT
    depth,
    LPAD('', depth * 4, ' ') || full_name  AS org_chart,   -- indent by level
    department,
    salary,
    reporting_path
FROM org_hierarchy
ORDER BY depth, full_name;

-- ============================================================
-- PATTERN 1: PostgreSQL — Force materialization to use CTE
-- as an optimization fence (prevents predicate pushdown intentionally)
-- Use case: The CTE calls a volatile function you only want to run once
-- ============================================================

WITH MATERIALIZED daily_exchange_rates AS (
    -- get_live_rate() hits an external FDW (foreign data wrapper) — expensive!
    -- Without MATERIALIZED, if referenced 3x, it could be called 3x.
    SELECT
        currency_code,
        get_live_rate(currency_code) AS usd_rate,  -- volatile external call
        CURRENT_TIMESTAMP            AS rate_fetched_at
    FROM supported_currencies
    WHERE is_active = true
)
SELECT
    o.order_id,
    o.order_total * der.usd_rate AS total_usd,
    der.rate_fetched_at
FROM orders o
JOIN daily_exchange_rates der ON der.currency_code = o.currency_code
WHERE o.order_status = 'pending';

-- ============================================================
-- PATTERN 2: SQL Server — CTE referenced multiple times.
-- DANGEROUS: SQL Server may re-run expensive_aggregation 3 times.
-- ============================================================

-- ANTI-PATTERN (avoid when CTE is expensive and referenced multiple times)
WITH expensive_aggregation AS (
    SELECT
        region_id,
        SUM(revenue)  AS total_revenue,
        AVG(revenue)  AS avg_revenue,
        COUNT(*)      AS transaction_count
    FROM sales_transactions
    WHERE transaction_date >= '2024-01-01'  -- scanning millions of rows
    GROUP BY region_id
)
SELECT 'Top'    AS tier, * FROM expensive_aggregation WHERE total_revenue > 1000000
UNION ALL
SELECT 'Mid'   AS tier, * FROM expensive_aggregation WHERE total_revenue BETWEEN 250000 AND 1000000
UNION ALL
SELECT 'Low'   AS tier, * FROM expensive_aggregation WHERE total_revenue < 250000;
-- ^ SQL Server may scan sales_transactions THREE TIMES for this query

-- BETTER PATTERN for SQL Server: use a temp table for expensive CTEs
-- referenced multiple times
SELECT
    region_id,
    SUM(revenue)  AS total_revenue,
    AVG(revenue)  AS avg_revenue,
    COUNT(*)      AS transaction_count
INTO #region_aggregates          -- materialize ONCE into temp table
FROM sales_transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY region_id;

SELECT 'Top' AS tier, * FROM #region_aggregates WHERE total_revenue > 1000000
UNION ALL
SELECT 'Mid' AS tier, * FROM #region_aggregates WHERE total_revenue BETWEEN 250000 AND 1000000
UNION ALL
SELECT 'Low' AS tier, * FROM #region_aggregates WHERE total_revenue < 250000;

DROP TABLE #region_aggregates;  -- clean up

-- ============================================================
-- PATTERN A: CTE + Window Function Pipeline
-- Find products in the bottom 10th percentile of sales velocity
-- so the warehouse team can flag them for clearance review
-- ============================================================

WITH product_sales_velocity AS (
    -- Step 1: Calculate units sold per day since launch for each product
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        p.launch_date,
        SUM(oi.quantity)                                         AS units_sold,
        CURRENT_DATE - p.launch_date                             AS days_since_launch,
        -- avoid division by zero for products launched today
        ROUND(
            SUM(oi.quantity)::NUMERIC /
            NULLIF(CURRENT_DATE - p.launch_date, 0),
        2)                                                       AS daily_velocity
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    LEFT JOIN orders o ON o.order_id = oi.order_id
        AND o.order_status = 'completed'
    WHERE p.is_active = true
      AND p.launch_date < CURRENT_DATE - INTERVAL '30 days'  -- exclude brand-new products
    GROUP BY p.product_id, p.product_name, p.category, p.launch_date
),

velocity_with_percentile AS (
    -- Step 2: Add percentile rank WITHIN each category
    -- We can't filter on PERCENT_RANK directly in the same SELECT — hence the CTE
    SELECT
        product_id,
        product_name,
        category,
        units_sold,
        days_since_launch,
        daily_velocity,
        PERCENT_RANK() OVER (
            PARTITION BY category            -- rank within category, not globally
            ORDER BY daily_velocity ASC      -- ascending: slowest movers rank lowest
        )                                                        AS velocity_percentile
    FROM product_sales_velocity
)

-- Step 3: Outer query — only pull the slow movers for clearance flagging
SELECT
    product_id,
    product_name,
    category,
    daily_velocity,
    ROUND(velocity_percentile * 100, 1)     AS percentile_position,
    'CLEARANCE_REVIEW'                      AS recommended_action
FROM velocity_with_percentile
WHERE velocity_percentile <= 0.10           -- bottom 10% within their category
ORDER BY category, daily_velocity ASC;

-- ============================================================
-- PATTERN B: Data-Modifying CTE (PostgreSQL)
-- Archive completed orders older than 2 years atomically:
-- insert into archive table, then delete from live table
-- Both happen in one transaction — no risk of double data or data loss
-- ============================================================

WITH orders_to_archive AS (
    -- Identify candidates
    SELECT order_id
    FROM orders
    WHERE order_status = 'completed'
      AND order_date < CURRENT_DATE - INTERVAL '2 years'
),

archived_orders AS (
    -- DELETE from live table, RETURNING the deleted rows
    -- In PostgreSQL, RETURNING lets data-modifying CTEs pass rows forward
    DELETE FROM orders
    WHERE order_id IN (SELECT order_id FROM orders_to_archive)
    RETURNING *    -- return all columns of deleted rows
)

-- INSERT the returned rows into the archive table
INSERT INTO orders_archive
SELECT
    *,
    CURRENT_TIMESTAMP AS archived_at    -- add archival timestamp
FROM archived_orders
RETURNING order_id, archived_at;        -- confirm what was archived