-- Template: Multi-Step CTEs for Complex Analysis
-- Pattern: Break complex queries into logical steps
-- Use Case: Complex calculations, data transformation pipelines

WITH 
-- Step 1: Calculate customer metrics
customer_metrics AS (
    SELECT 
        c.customer_id,
        c.name,
        COUNT(o.order_id) as total_orders,
        SUM(o.total_amount) as total_spent,
        MIN(o.order_date) as first_order_date,
        MAX(o.order_date) as last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.name
),
-- Step 2: Calculate overall averages
overall_stats AS (
    SELECT 
        AVG(total_spent) as avg_customer_value,
        AVG(total_orders) as avg_orders_per_customer
    FROM customer_metrics
),
-- Step 3: Classify customers
customer_segments AS (
    SELECT 
        cm.*,
        os.avg_customer_value,
        CASE 
            WHEN cm.total_spent > os.avg_customer_value * 2 THEN 'VIP'
            WHEN cm.total_spent > os.avg_customer_value THEN 'High Value'
            WHEN cm.total_spent > 0 THEN 'Regular'
            ELSE 'Inactive'
        END as segment
    FROM customer_metrics cm
    CROSS JOIN overall_stats os
)
-- Final output
SELECT 
    segment,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spent,
    AVG(total_orders) as avg_orders
FROM customer_segments
GROUP BY segment
ORDER BY avg_spent DESC;

-- Key Concepts:
-- - WITH clause defines multiple CTEs (Common Table Expressions)
-- - Each CTE builds on previous ones
-- - Better readability than nested subqueries
-- - Can reference previous CTEs in later ones
-- - Improves maintainability and debugging