SLA breaches by priority

Every ticket priority has a resolve target in slas.resolve_hours. Support leads want to know how often each priority misses it.

Only look at closed tickets (closed_at is not NULL). Open tickets haven’t finished yet, so they can’t have breached the resolve target in this report.

A closed ticket breached if it took longer than the target: closed_at is strictly later than opened_at + resolve_hours. Closing exactly on the deadline is not a breach.

Return one row per priority that has at least one closed ticket:

column	type	meaning
priority	text	the ticket priority
closed_tickets	integer	closed tickets with that priority
breached	integer	how many of those breached (0 if none)
breach_pct	decimal	breached / closed_tickets * 100, rounded to 1 decimal

A priority with zero breaches still gets a row, with breached = 0.

Row order doesn’t matter.

Tables: tickets, slas.

Approach

Join each ticket to its SLA row, keep closed tickets, group by priority, and count breaches with a filtered aggregate.

SELECT t.priority,
       COUNT(*) AS closed_tickets,
       COUNT(*) FILTER (WHERE t.closed_at > t.opened_at + s.resolve_hours * INTERVAL '1 hour') AS breached,
       ROUND(100.0 * COUNT(*) FILTER (WHERE t.closed_at > t.opened_at + s.resolve_hours * INTERVAL '1 hour')
             / COUNT(*), 1) AS breach_pct
FROM tickets t
JOIN slas s ON s.priority = t.priority
WHERE t.closed_at IS NOT NULL
GROUP BY t.priority;

Why it works

There are two filters here, and they belong in different places. “Closed tickets only” decides which rows exist, so it goes in WHERE. “Breached” is one count among several, so it goes in a FILTER on that one aggregate. Put it in WHERE and every priority with zero breaches drops out of the result.

The comparison is >, not >=, because closing exactly on the deadline meets the target. Multiplying an integer by INTERVAL '1 hour' gives an interval, so you compare timestamps directly and don’t round anything to whole hours.

Under the hood

slas has four rows. DuckDB builds its hash table on the small side of the join (the four SLA rows) and streams the 800 tickets past it. Each ticket probes once, picks up its resolve_hours, and goes straight into the aggregation hash table keyed by priority. The interval arithmetic is a per-row add on 64-bit microsecond counts, which is why timestamps compare so cheaply.