04_examples.sql

-- Queries over saved scores; no new model calls. Thresholds are illustrative.

-- 1. Team summary.
SELECT
  route,
  COUNT(*) AS active_tickets,
  COUNTIF(urgent_probability >= 0.80) AS urgent_tickets,
  COUNTIF(blocked_probability >= 0.80) AS blocked_tickets
FROM jev_demo.enriched_tickets
WHERE status != 'closed'
GROUP BY route
ORDER BY active_tickets DESC, route;

-- 2. Urgent tickets with blocked workflows (the blog opening).
SELECT ticket_id, subject, route, urgent_probability, blocked_probability
FROM jev_demo.enriched_tickets
WHERE status != 'closed'
  AND urgent_probability >= 0.80
  AND blocked_probability >= 0.80
ORDER BY ticket_id;

-- Optional: review queues for uncertain ownership or impact.
SELECT
  ticket_id, subject, route, urgent_probability, blocked_probability,
  CASE
    WHEN route IS NULL OR urgent_probability IS NULL
      OR blocked_probability IS NULL THEN 'Missing result'
    WHEN route = 'other' OR route_confidence IS NULL
      OR route_confidence < 0.70 THEN 'Review ownership'
    WHEN urgent_probability >= 0.80 AND blocked_probability >= 0.80
      THEN 'Urgent and blocked'
    WHEN urgent_probability >= 0.80 THEN 'Urgent, assess impact'
    WHEN urgent_probability BETWEEN 0.20 AND 0.80
      OR blocked_probability BETWEEN 0.20 AND 0.80 THEN 'Review impact'
    WHEN blocked_probability >= 0.80 THEN 'Blocked, confirm urgency'
    ELSE 'Normal queue'
  END AS review_queue
FROM jev_demo.enriched_tickets
WHERE status != 'closed'
ORDER BY ticket_id;

-- Optional: closed tickets with weak evidence of resolution.
SELECT ticket_id, subject, resolution, resolution_supported_probability
FROM jev_demo.enriched_tickets
WHERE status = 'closed'
  AND (resolution_supported_probability < 0.50
       OR resolution_supported_probability IS NULL)
ORDER BY ticket_id;