Stop Tagging. Start Understanding.

-- AI Agent Generated: Support Cost vs Customer Tier (Zendesk + Stripe)
-- Dialect: Standard PostgreSQL / Snowflake
-- Target: Calculate average ticket handling time and volume, grouped by Stripe MRR.

WITH parsed_tickets AS (
    SELECT 
        id AS ticket_id,
        created_at,
        -- Safely extract email from nested requester JSONB
        requester_info->>'email' AS customer_email,
        -- COMPLEXITY: Zendesk stores custom fields as an array of objects [{"id": 123, "value": "x"}]
        -- We must unnest the array to find the specific "Category" field (assuming ID 360012345678)
        (
            SELECT cf->>'value' 
            FROM jsonb_array_elements(custom_fields) cf 
            WHERE cf->>'id' = '360012345678'
        ) AS ticket_category,
        -- Calculate total time open in hours
        EXTRACT(EPOCH FROM (updated_at - created_at))/3600.0 AS resolution_hours
    FROM tenant_workspace.zendesk.tickets
    WHERE status IN ('solved', 'closed')
      AND created_at >= CURRENT_DATE - INTERVAL '90 days'
),
stripe_customers AS (
    SELECT 
        c.email,
        s.status AS sub_status,
        -- Calculate MRR in dollars
        SUM(s.plan_amount_cents) / 100.0 AS current_mrr,
        -- Dynamically bucket customers into revenue tiers
        CASE 
            WHEN SUM(s.plan_amount_cents) / 100.0 >= 1000 THEN 'Enterprise ($1k+)'
            WHEN SUM(s.plan_amount_cents) / 100.0 >= 100 THEN 'Pro ($100+)'
            ELSE 'Basic/Free'
        END AS customer_tier
    FROM tenant_workspace.stripe.customers c
    JOIN tenant_workspace.stripe.subscriptions s ON c.id = s.customer_id
    WHERE s.status = 'active'
    GROUP BY c.email, s.status
),
cross_platform_join AS (
    -- Bridge Zendesk and Stripe via normalized email
    SELECT 
        t.ticket_id,
        t.ticket_category,
        t.resolution_hours,
        COALESCE(s.customer_tier, 'Unknown/Non-Subscriber') AS customer_tier,
        COALESCE(s.current_mrr, 0) AS mrr
    FROM parsed_tickets t
    LEFT JOIN stripe_customers s ON LOWER(TRIM(t.customer_email)) = LOWER(TRIM(s.email))
)
-- Aggregate metrics for executive view
SELECT 
    customer_tier,
    COUNT(DISTINCT ticket_id) AS total_tickets,
    ROUND(AVG(resolution_hours)::numeric, 2) AS avg_resolution_hours,
    SUM(mrr) AS total_mrr_impacted,
    -- Calculate efficiency ratio (Tickets generated per $1k of MRR)
    CASE 
        WHEN SUM(mrr) = 0 THEN 0
        ELSE ROUND((COUNT(DISTINCT ticket_id) / (SUM(mrr) / 1000))::numeric, 2) 
    END AS tickets_per_1k_mrr
FROM cross_platform_join
GROUP BY 1
ORDER BY avg_resolution_hours DESC;