-- Single CTE
WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE condition
)
SELECT *
FROM cte_name;

-- Multiple CTEs
WITH 
cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ...
    FROM cte1  -- Can reference previous CTEs
)
SELECT *
FROM cte1
JOIN cte2 ON

-- Without CTE (harder to read)
SELECT 
    c.customer_id,
    c.name,
    order_stats.total_orders,
    order_stats.total_spent
FROM customers c
JOIN (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        SUM(total) as total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
) order_stats ON c.customer_id = order_stats.customer_id;

-- With CTE (more readable)
WITH order_stats AS (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        SUM(total) as total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT 
    c.customer_id,
    c.name,
    os.total_orders,
    os.total_spent
FROM customers c
JOIN order_stats os ON

WITH 
-- First CTE: Get monthly sales
monthly_sales AS (
    SELECT 
        DATE_TRUNC('MONTH', order_date) as month,
        SUM(total) as revenue,
        COUNT(*) as order_count
    FROM orders
    GROUP BY DATE_TRUNC('MONTH', order_date)
),
-- Second CTE: Calculate month-over-month change
sales_with_growth AS (
    SELECT 
        month,
        revenue,
        order_count,
        LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
        revenue - LAG(revenue) OVER (ORDER BY month) as revenue_change,
        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 
            / LAG(revenue) OVER (ORDER BY month) as growth_rate_pct
    FROM monthly_sales
),
-- Third CTE: Categorize performance
performance_rated AS (
    SELECT 
        *,
        CASE 
            WHEN growth_rate_pct > 10 THEN 'Excellent'
            WHEN growth_rate_pct > 0 THEN 'Good'
            WHEN growth_rate_pct > -10 THEN 'Declining'
            ELSE 'Poor'
        END as performance_rating
    FROM sales_with_growth
)
SELECT 
    month,
    revenue,
    order_count,
    COALESCE(revenue_change, 0) as revenue_change,
    COALESCE(ROUND(growth_rate_pct, 2), 0) as growth_rate_pct,
    performance_rating
FROM performance_rated
ORDER BY month

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Start with CEO (no manager)
    SELECT 
        employee_id,
        name,
        manager_id,
        title,
        1 as level,
        CAST(name AS VARCHAR(1000)) as hierarchy_path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Get employees reporting to previous level
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        e.title,
        eh.level + 1,
        CAST(eh.hierarchy_path || ' > ' || e.name AS VARCHAR(1000))
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id,
    name,
    title,
    level,
    hierarchy_path
FROM employee_hierarchy
ORDER BY level,

WITH RECURSIVE date_series AS (
    -- Anchor: Start date
    SELECT DATE '2024-01-01' as date
    
    UNION ALL
    
    -- Recursive: Add one day until end date
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_series
    WHERE date < DATE '2024-01-31'
)
SELECT 
    date,
    DAYNAME(date) as day_of_week,
    CASE 
        WHEN DAYNAME(date) IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END as day_type
FROM date_series
ORDER BY

WITH 
-- Get customer order summary
customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        SUM(total) as lifetime_value,
        MIN(order_date) as first_order_date,
        MAX(order_date) as last_order_date
    FROM orders
    GROUP BY customer_id
),
-- Categorize customers
customer_segments AS (
    SELECT 
        co.*,
        CASE 
            WHEN lifetime_value > 1000 THEN 'VIP'
            WHEN lifetime_value > 500 THEN 'Premium'
            ELSE 'Standard'
        END as segment,
        DATEDIFF(CURRENT_DATE, last_order_date) as days_since_last_order
    FROM customer_orders co
),
-- Calculate segment statistics
segment_stats AS (
    SELECT 
        segment,
        COUNT(*) as customer_count,
        AVG(lifetime_value) as avg_lifetime_value,
        AVG(total_orders) as avg_orders_per_customer,
        AVG(days_since_last_order) as avg_days_since_last_order
    FROM customer_segments
    GROUP BY segment
)
SELECT 
    cs.segment,
    cs.customer_count,
    ROUND(cs.avg_lifetime_value, 2) as avg_lifetime_value,
    ROUND(cs.avg_orders_per_customer, 1) as avg_orders,
    ROUND(cs.avg_days_since_last_order, 0) as avg_days_inactive,
    ROUND(cs.customer_count * 100.0 / SUM(cs.customer_count) OVER (), 2) as pct_of_customers
FROM segment_stats cs
ORDER BY avg_lifetime_value DESC