First-response SLA by channel

Each priority has a first-response target in slas.first_response_hours. The support lead wants to know which channel (email, web, chat) meets it most often.

A ticket’s first response is the earliest ticket_events row for that ticket with event_type = 'first_response'. A ticket met the SLA if that first response happened no later than opened_at + first_response_hours. Exactly on the deadline counts as met.

A ticket with no first-response event did not meet the SLA, but it still counts in tickets.

Return one row per channel:

column	type	meaning
channel	text	the channel the ticket came in on
tickets	integer	all tickets on that channel
met_sla	integer	tickets that met their first-response target
met_pct	decimal	met_sla / tickets * 100, rounded to 1 decimal

Row order doesn’t matter.

Tables: tickets, ticket_events, slas.

Approach

Three steps, each a CTE or the final SELECT: earliest response per ticket, grade each ticket, count per channel.

WITH first_response AS (
  SELECT ticket_id, MIN(event_ts) AS responded_at
  FROM ticket_events
  WHERE event_type = 'first_response'
  GROUP BY ticket_id
),
graded AS (
  SELECT t.channel,
         COALESCE(fr.responded_at <= t.opened_at + s.first_response_hours * INTERVAL '1 hour', FALSE) AS met
  FROM tickets t
  JOIN slas s ON s.priority = t.priority
  LEFT JOIN first_response fr ON fr.ticket_id = t.ticket_id
)
SELECT channel,
       COUNT(*) AS tickets,
       COUNT(*) FILTER (WHERE met) AS met_sla,
       ROUND(100.0 * COUNT(*) FILTER (WHERE met) / COUNT(*), 1) AS met_pct
FROM graded
GROUP BY channel;

Why it works

Each CTE fixes the grain before the next step uses it. first_response has exactly one row per ticket, so joining it can’t duplicate tickets even if an event was logged twice. Joining ticket_events directly would count such a ticket twice.

The LEFT JOIN keeps tickets that never got a response. For them the comparison is NULL <= deadline, which is NULL, and FILTER (WHERE met) would skip it anyway. COALESCE(..., FALSE) makes the miss explicit so met is never unknown.

Under the hood

A CTE is a name, not a temp table. DuckDB inlines first_response and graded into one plan: a hash aggregate over ticket_events (building one MIN per ticket), two hash joins, then a hash aggregate by channel. The optimizer is free to reorder joins across CTE boundaries, so writing in steps costs nothing at run time.