Drill 4 of 12 in SaaS questions
0

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…