support_analytics.sql

-- Analytical SQL for Customer Support Tickets 200K+
-- Assumes a table named support_tickets with the same columns as data/raw/customer_support_tickets_200k.csv.

-- 1. Tickets by channel
SELECT
    channel,
    COUNT(*) AS tickets
FROM support_tickets
GROUP BY channel
ORDER BY tickets DESC;

-- 2. Categories and SLA risk
SELECT
    category,
    COUNT(*) AS tickets,
    AVG(CASE WHEN sla_breached = 'Yes' THEN 1.0 ELSE 0.0 END) AS sla_breach_rate,
    AVG(first_response_time_hours) AS avg_first_response_h,
    AVG(resolution_time_hours) AS avg_resolution_h,
    AVG(customer_satisfaction_score) AS avg_csat
FROM support_tickets
GROUP BY category
ORDER BY sla_breach_rate DESC, tickets DESC;

-- 3. Product-level bottlenecks
SELECT
    product,
    priority,
    COUNT(*) AS tickets,
    AVG(first_response_time_hours) AS avg_first_response_h,
    AVG(resolution_time_hours) AS avg_resolution_h,
    AVG(CASE WHEN escalated = 'Yes' THEN 1.0 ELSE 0.0 END) AS escalation_rate,
    AVG(CASE WHEN sla_breached = 'Yes' THEN 1.0 ELSE 0.0 END) AS sla_breach_rate
FROM support_tickets
GROUP BY product, priority
ORDER BY sla_breach_rate DESC, avg_first_response_h DESC;

-- 4. Monthly dynamics
SELECT
    DATE_TRUNC('month', CAST(ticket_created_date AS DATE)) AS month,
    COUNT(*) AS tickets,
    AVG(first_response_time_hours) AS avg_first_response_h,
    AVG(resolution_time_hours) AS avg_resolution_h,
    AVG(CASE WHEN sla_breached = 'Yes' THEN 1.0 ELSE 0.0 END) AS sla_breach_rate,
    AVG(customer_satisfaction_score) AS avg_csat
FROM support_tickets
GROUP BY 1
ORDER BY 1;

-- 5. CSAT impact by response-time bucket
SELECT
    CASE
        WHEN first_response_time_hours < 4 THEN '<4h'
        WHEN first_response_time_hours < 12 THEN '4-12h'
        WHEN first_response_time_hours < 24 THEN '12-24h'
        WHEN first_response_time_hours < 48 THEN '24-48h'
        ELSE '48h+'
    END AS first_response_bucket,
    COUNT(*) AS tickets,
    AVG(customer_satisfaction_score) AS avg_csat,
    AVG(CASE WHEN sla_breached = 'Yes' THEN 1.0 ELSE 0.0 END) AS sla_breach_rate
FROM support_tickets
GROUP BY 1
ORDER BY MIN(first_response_time_hours);

-- 6. Routing queue proposal
SELECT
    ticket_id,
    category,
    product,
    priority,
    issue_complexity_score,
    customer_segment,
    subscription_type,
    CASE
        WHEN priority = 'Urgent'
             OR issue_complexity_score >= 8
             OR customer_segment = 'Corporate'
             OR subscription_type = 'Enterprise'
            THEN 'Tier 2/3 specialist queue'
        WHEN priority = 'High'
             OR issue_complexity_score >= 5
            THEN 'Tier 1 priority queue'
        ELSE 'Tier 1 standard queue'
    END AS recommended_queue
FROM support_tickets;