Business Problem

Zendesk's support operations lead is reviewing a spike in customer complaints about slow responses and needs to know exactly how badly the team is missing its SLAs. For every ticket, calculate actual response and resolution times against the SLA thresholds tied to that ticket's priority level, flag whether each stage breached its SLA and by how many hours, and label the type of violation (response, resolution, both, or none) — so leadership can see whether the problem is concentrated in high-priority tickets, a specific category, or across the board.

Dataset & Schema

Support tickets with ticket_id, category, priority, created_at, first_response_at, resolved_at; SLA config with priority, response_sla_hours, resolution_sla_hours.

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY,
  ticket_category TEXT,
  priority TEXT,
  created_at DATETIME,
  first_response_at DATETIME,
  resolved_at DATETIME
);
CREATE TABLE sla_config (
  priority TEXT PRIMARY KEY,
  response_sla_hours INTEGER,
  resolution_sla_hours INTEGER
);

SQL Solution

WITH ticket_sla_metrics AS (
  SELECT 
    t.ticket_id,
    t.ticket_category,
    t.priority,
    t.created_at,
    t.first_response_at,
    t.resolved_at,
    s.response_sla_hours,
    s.resolution_sla_hours,
    ROUND((julianday(t.first_response_at) - julianday(t.created_at)) * 24, 2) as response_time_hours,
    ROUND((julianday(t.resolved_at) - julianday(t.created_at)) * 24, 2) as resolution_time_hours
  FROM tickets t
  INNER JOIN sla_config s ON t.priority = s.priority
),
sla_compliance AS (
  SELECT 
    ticket_id,
    ticket_category,
    priority,
    response_time_hours,
    resolution_time_hours,
    response_sla_hours,
    resolution_sla_hours,
    CASE 
      WHEN response_time_hours <= response_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as response_sla_status,
    CASE 
      WHEN resolution_time_hours <= resolution_sla_hours THEN 'Met'
      ELSE 'Violated'
    END as resolution_sla_status,
    ROUND(response_time_hours - response_sla_hours, 2) as response_breach_hours,
    ROUND(resolution_time_hours - resolution_sla_hours, 2) as resolution_breach_hours
  FROM ticket_sla_metrics
)
SELECT 
  ticket_id,
  ticket_category,
  priority,
  response_time_hours,
  resolution_time_hours,
  response_sla_hours,
  resolution_sla_hours,
  response_sla_status,
  resolution_sla_status,
  CASE 
    WHEN response_sla_status = 'Violated' OR resolution_sla_status = 'Violated' THEN 'YES'
    ELSE 'NO'
  END as sla_violation,
  response_breach_hours,
  resolution_breach_hours,
  CASE 
    WHEN response_sla_status = 'Violated' AND resolution_sla_status = 'Violated' THEN 'Both-SLAs-Missed'
    WHEN response_sla_status = 'Violated' THEN 'Response-SLA-Missed'
    WHEN resolution_sla_status = 'Violated' THEN 'Resolution-SLA-Missed'
    ELSE 'Compliant'
  END as violation_type
FROM sla_compliance
ORDER BY sla_violation DESC, response_breach_hours DESC;

Explanation