SLA hit rate by metric

slas stores two targets per priority side by side: first_response_hours and resolve_hours. That wide shape is fine for reading, but awkward for reporting, where you want one row per priority and metric.

Report how often each target was met. Return one row per (priority, metric) with:

column	type	meaning
priority	text	the priority
metric	text	'first_response' or 'resolve'
target_hours	integer	the target from slas
tickets	integer	tickets measured for this metric
met_pct	decimal	percentage of those tickets that met the target, rounded to 1 decimal

How to measure:

• first_response: from the ticket’s opened_at to its earliest first_response event in ticket_events. Tickets with no first_response event aren’t measured.
• resolve: from opened_at to closed_at. Open tickets (closed_at is NULL) aren’t measured.
• A ticket meets a target when the time taken is less than or equal to the target. Exactly on target counts as met.
• Leave out any (priority, metric) pair with no measured tickets.

Row order doesn’t matter.

Tables: tickets, ticket_events, slas.

Approach

Unpivot the targets into one row per (priority, metric). Shape the measurements the same way. Join on both keys and aggregate.

WITH targets AS (
  -- unpivot slas: one row per (priority, metric)
  SELECT s.priority,
         m.metric,
         CASE m.metric
           WHEN 'first_response' THEN s.first_response_hours
           WHEN 'resolve'        THEN s.resolve_hours
         END AS target_hours
  FROM slas s
  CROSS JOIN (VALUES ('first_response'), ('resolve')) AS m(metric)
),
first_responses AS (
  SELECT ticket_id, MIN(event_ts) AS responded_at
  FROM ticket_events
  WHERE event_type = 'first_response'
  GROUP BY ticket_id
),
elapsed AS (
  -- the measurements in the same long shape: one row per (ticket, metric)
  SELECT t.priority, 'first_response' AS metric, f.responded_at - t.opened_at AS took
  FROM tickets t
  JOIN first_responses f ON f.ticket_id = t.ticket_id
  UNION ALL
  SELECT t.priority, 'resolve' AS metric, t.closed_at - t.opened_at AS took
  FROM tickets t
  WHERE t.closed_at IS NOT NULL
)
SELECT g.priority,
       g.metric,
       g.target_hours,
       COUNT(*) AS tickets,
       ROUND(100.0 * COUNT(*) FILTER (WHERE e.took <= to_hours(g.target_hours)) / COUNT(*), 1) AS met_pct
FROM targets g
JOIN elapsed e ON e.priority = g.priority AND e.metric = g.metric
GROUP BY g.priority, g.metric, g.target_hours;

Why it works

Unpivoting turns column names into values. Once the metric is a value, “first response” and “resolve” stop being two special cases with two code paths. They’re two keys in one join, and a third metric later is one more row in the VALUES list and one more branch of the UNION ALL.

The cross join makes two copies of every SLA row, one per metric. The CASE picks the column that matches the copy. That’s all UNPIVOT does internally.

The inner join does the “leave out” rule for free. A priority with no measured tickets for a metric has no rows in elapsed, so its target row finds no partner and drops out.

took stays an interval inside the query and is compared to to_hours(target_hours), so a ticket answered 4 hours and 30 seconds after opening misses a 4-hour target. The interval never reaches the output.

MIN(event_ts) guards against a ticket with more than one first_response event. Today there’s one per ticket, but the rule says “earliest”, and the data may not stay this clean.

Under the hood

The cross join is tiny: 4 SLA rows × 2 metrics. The heavy work is the ticket_events scan with a hash aggregate for MIN, a hash join to tickets, and a UNION ALL, which just concatenates two streams with no deduplication. The final join probes a hash table of 8 target rows, and the GROUP BY keeps two counters per group.