WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE conditions
)
SELECT * FROM

SELECT
    d.department_name,
    (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) as avg_salary,
    (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) as employee_count,
    (SELECT MAX(salary) FROM employees e WHERE e.department_id = d.id) as max_salary
FROM departments d
WHERE (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) > 50000

WITH department_stats AS (
    SELECT
        department_id,
        AVG(salary) as avg_salary,
        COUNT(*) as employee_count,
        MAX(salary) as max_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    d.department_name,
    ds.avg_salary,
    ds.employee_count,
    ds.max_salary
FROM departments d
JOIN department_stats ds ON d.id = ds.department_id
WHERE ds.avg_salary > 50000

WITH active_customers AS (
    SELECT customer_id, name, email
    FROM customers
    WHERE status = 'active'
    AND last_order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT * FROM active_customers ORDER BY

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as revenue,
        COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    order_count,
    revenue / order_count as avg_order_value
FROM monthly_sales
ORDER BY month

WITH
    high_value_customers AS (
        SELECT customer_id
        FROM orders
        GROUP BY customer_id
        HAVING SUM(total_amount) > 10000
    ),
    recent_orders AS (
        SELECT customer_id, order_id, order_date, total_amount
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    )
SELECT
    c.name,
    c.email,
    ro.order_date,
    ro.total_amount
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id
JOIN recent_orders ro ON c.id = ro.customer_id
ORDER BY ro.order_date DESC

WITH RECURSIVE cte_name AS (
    -- Base case (anchor member)
    SELECT columns FROM table WHERE condition

    UNION ALL

    -- Recursive case
    SELECT columns FROM table
    JOIN cte_name ON relationship
)
SELECT * FROM

WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers (no manager_id)
    SELECT
        id,
        name,
        title,
        manager_id,
        1 as level,
        name as path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    SELECT
        e.id,
        e.name,
        e.title,
        e.manager_id,
        ot.level + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', level - 1) || name as employee,
    title,
    level,
    path
FROM org_tree
ORDER BY path

WITH RECURSIVE category_tree AS (
    SELECT
        id,
        name,
        parent_id,
        ARRAY[name] as path,
        1 as depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        ct.path || c.name,
        ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    id,
    name,
    array_to_string(path, ' > ') as full_path,
    depth
FROM category_tree
ORDER BY path

WITH daily_sales AS (
    SELECT
        DATE(order_date) as sale_date,
        SUM(total_amount) as daily_total
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY DATE(order_date)
)
SELECT
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date) as running_total
FROM daily_sales
ORDER BY

WITH current_year AS (
    SELECT
        EXTRACT(MONTH FROM order_date) as month,
        SUM(total_amount) as revenue
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2025
    GROUP BY EXTRACT(MONTH FROM order_date)
),
previous_year AS (
    SELECT
        EXTRACT(MONTH FROM order_date) as month,
        SUM(total_amount) as revenue
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2024
    GROUP BY EXTRACT(MONTH FROM order_date)
)
SELECT
    cy.month,
    cy.revenue as revenue_2025,
    py.revenue as revenue_2024,
    ROUND((cy.revenue - py.revenue) / py.revenue * 100, 2) as yoy_growth_pct
FROM current_year cy
LEFT JOIN previous_year py ON cy.month = py.month
ORDER BY

WITH ranked_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC
        ) as rn
    FROM customers
)
SELECT id, email, name, created_at
FROM ranked_records
WHERE rn = 1

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_data AS (
    SELECT
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) as order_month,
        COUNT(DISTINCT o.customer_id) as customers
    FROM first_purchase fp
    JOIN orders o ON fp.customer_id = o.customer_id
    GROUP BY fp.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
    cohort_month,
    order_month,
    customers,
    EXTRACT(MONTH FROM AGE(order_month, cohort_month)) as months_since_first
FROM cohort_data
ORDER BY cohort_month,

WITH active_users AS NOT MATERIALIZED (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2025-01-01'

WITH customer_metrics AS (
    SELECT
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as lifetime_value,
        MAX(order_date) as last_order,
        MIN(order_date) as first_order
    FROM orders
    GROUP BY customer_id
),
segments AS (
    SELECT
        customer_id,
        lifetime_value,
        order_count,
        CASE
            WHEN lifetime_value >= 10000 AND order_count >= 10 THEN 'VIP'
            WHEN lifetime_value >= 5000 OR order_count >= 5 THEN 'Regular'
            WHEN last_order >= CURRENT_DATE - INTERVAL '90 days' THEN 'Active'
            ELSE 'At Risk'
        END as segment
    FROM customer_metrics
)
SELECT
    segment,
    COUNT(*) as customer_count,
    AVG(lifetime_value) as avg_ltv,
    SUM(lifetime_value) as total_revenue
FROM segments
GROUP BY segment
ORDER BY total_revenue DESC

WITH stock_status AS (
    SELECT
        product_id,
        quantity_on_hand,
        reorder_point,
        CASE
            WHEN quantity_on_hand <= 0 THEN 'Out of Stock'
            WHEN quantity_on_hand <= reorder_point THEN 'Low Stock'
            ELSE 'In Stock'
        END as status
    FROM inventory
),
recent_sales AS (
    SELECT
        product_id,
        SUM(quantity) as units_sold_30d
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY product_id
)
SELECT
    p.name,
    ss.quantity_on_hand,
    ss.status,
    COALESCE(rs.units_sold_30d, 0) as units_sold_30d,
    CASE
        WHEN rs.units_sold_30d > 0
        THEN ROUND(ss.quantity_on_hand::numeric / (rs.units_sold_30d / 30.0), 1)
        ELSE NULL
    END as days_of_stock
FROM products p
JOIN stock_status ss ON p.id = ss.product_id
LEFT JOIN recent_sales rs ON p.id = rs.product_id
WHERE ss.status != 'In Stock'
ORDER BY days_of_stock NULLS FIRST

WITH funnel_stages AS (
    SELECT
        session_id,
        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as viewed,
        MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
        MAX(CASE WHEN event = 'checkout_started' THEN 1 ELSE 0 END) as started_checkout,
        MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) as purchased
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY session_id
)
SELECT
    SUM(viewed) as stage_1_views,
    SUM(added_to_cart) as stage_2_cart,
    SUM(started_checkout) as stage_3_checkout,
    SUM(purchased) as stage_4_purchase,
    ROUND(SUM(added_to_cart)::numeric / NULLIF(SUM(viewed), 0) * 100, 2) as view_to_cart_pct,
    ROUND(SUM(started_checkout)::numeric / NULLIF(SUM(added_to_cart), 0) * 100, 2) as cart_to_checkout_pct,
    ROUND(SUM(purchased)::numeric / NULLIF(SUM(started_checkout), 0) * 100, 2) as checkout_to_purchase_pct
FROM

-- Wrong: can't reference cte2 before it's defined
WITH cte1 AS (SELECT * FROM cte2), -- Error!
     cte2 AS (SELECT * FROM table)
SELECT * FROM cte1;

-- Correct: define in order of dependency
WITH cte2 AS (SELECT * FROM table),
     cte1 AS (SELECT * FROM cte2)
SELECT * FROM

-- CTE is not available in second query
WITH active_users AS (SELECT * FROM users WHERE status = 'active')
SELECT * FROM active_users;

SELECT * FROM active_users; -- Error: active_users doesn't exist here

-- Infinite loop risk
WITH RECURSIVE bad_cte AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM bad_cte -- Never stops!
)
SELECT * FROM bad_cte;

-- Always add a termination condition
WITH RECURSIVE good_cte AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM good_cte WHERE n < 100 -- Stops at 100
)
SELECT * FROM