Interview Question Bank · Timestamp intervals · NULL as breach · FILTER
4. Support SLA breach rate by priority
MediumSaaSQuestion #112
Concept: Timestamp intervals · NULL as breach · FILTERshow
Subtracting two timestamps gives an interval, and intervals compare directly: response - opened > 4 * INTERVAL '1 hour'. Don't pull out EXTRACT(HOUR ...), which reads only the hours field and ignores days. A missing response is NULL, and any comparison with NULL is NULL (not true), so unanswered rows need their own IS NULL check.
Example (different data):
SELECT carrier,
COUNT(*) FILTER (WHERE delivered_at IS NULL
OR delivered_at - shipped_at > promised_days * INTERVAL '1 day') AS late
FROM shipments
GROUP BY carrier;Your task
Tallyboard promises a first reply within a set number of hours per ticket priority. The support lead wants May 2025's breach rate for each priority. A ticket nobody has replied to yet counts as breached (all are older than their target).
Return priority, tickets, breached (tickets whose first response came more than response_hours after opened_at, or never came) and breach_rate_pct (breached as a % of tickets, rounded to 1 decimal). A reply exactly on the target is on time. Sort from the tightest target to the loosest (by response_hours).
Starting Postgres in your browser…
Checking your access…