How to Build a Multi-Step Analysis With CTEs (Step by Step)

WITH step_one AS (
    -- First calculation
),
step_two AS (
    -- Second calculation, can reference step_one
)
SELECT ...
FROM step_one
    JOIN step_two ON ...

-- Guide performance and expedition alignment analysis
-- Question: Which guides generate the most revenue, and are they 
-- assigned to the right expedition types?

-- Step 1: Calculate total revenue per guide
WITH guide_revenue AS (
    SELECT 
        g.guide_id,
        g.first_name || ' ' || g.last_name AS guide_name,
        g.specializations,
        g.years_experience,
        COUNT(DISTINCT ga.instance_id) AS trips_assigned,
        SUM(p.amount) AS total_revenue_generated
    FROM guides g
        INNER JOIN guide_assignments ga ON g.guide_id = ga.guide_id
        INNER JOIN expedition_instances ei ON ga.instance_id = ei.instance_id
        INNER JOIN bookings b ON ei.instance_id = b.instance_id
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
    GROUP BY g.guide_id, g.first_name, g.last_name, 
             g.specializations, g.years_experience
),

-- Step 2: Find each guide's primary expedition type (most assigned)
guide_primary_type AS (
    SELECT DISTINCT ON (g.guide_id)
        g.guide_id,
        e.expedition_type AS primary_type,
        COUNT(*) AS type_count
    FROM guides g
        INNER JOIN guide_assignments ga ON g.guide_id = ga.guide_id
        INNER JOIN expedition_instances ei ON ga.instance_id = ei.instance_id
        INNER JOIN expeditions e ON ei.expedition_id = e.expedition_id
    GROUP BY g.guide_id, e.expedition_type
    ORDER BY g.guide_id, COUNT(*) DESC
),

-- Step 3: Calculate average revenue by expedition type
type_benchmarks AS (
    SELECT 
        e.expedition_type,
        ROUND(AVG(p.amount), 2) AS avg_revenue_per_transaction,
        SUM(p.amount) AS total_type_revenue
    FROM expeditions e
        INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
        INNER JOIN bookings b ON ei.instance_id = b.instance_id
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE p.payment_status = 'completed'
    GROUP BY e.expedition_type
)

-- Step 4: Combine everything into the final picture
SELECT 
    gr.guide_name,
    gr.specializations,
    gr.years_experience,
    gr.trips_assigned,
    gr.total_revenue_generated,
    gpt.primary_type AS most_assigned_type,
    tb.avg_revenue_per_transaction AS type_avg,
    ROUND(gr.total_revenue_generated / NULLIF(gr.trips_assigned, 0), 2) 
        AS guide_revenue_per_trip,
    CASE 
        WHEN gr.total_revenue_generated / NULLIF(gr.trips_assigned, 0) > 
             tb.avg_revenue_per_transaction * 1.2 
        THEN 'Above Average'
        WHEN gr.total_revenue_generated / NULLIF(gr.trips_assigned, 0) < 
             tb.avg_revenue_per_transaction * 0.8 
        THEN 'Below Average'
        ELSE 'On Track'
    END AS performance_label
FROM guide_revenue gr
    INNER JOIN guide_primary_type gpt ON gr.guide_id = gpt.guide_id
    INNER JOIN type_benchmarks tb ON gpt.primary_type = tb.expedition_type
ORDER BY gr.total_revenue_generated DESC
LIMIT 15;

WITH calculations AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payments GROUP BY customer_id
)
SELECT * FROM calculations WHERE total_spent > 5000;

WITH volume AS (...),  -- How many?
     value AS (...),   -- How much?
     timing AS (...)   -- When?
SELECT ...
FROM volume JOIN value ON ... JOIN timing ON ...;

WITH raw_data AS (...),          -- Base records
     with_labels AS (...),        -- Add CASE WHEN categories
     with_benchmarks AS (...)     -- Add comparison metrics
SELECT * FROM with_benchmarks;