First-Response SLA Breach Rate by Priority — SQL Problem
The support SLA promises a first reply within 60 minutes for an 'urgent' ticket, 240 minutes for 'high' and 1440 minutes for 'normal'.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #331
Problem statement
The support SLA promises a first reply within 60 minutes for an 'urgent' ticket, 240 minutes for 'high' and 1440 minutes for 'normal'. A ticket breaches when its first response came more than its target after created_at (a reply exactly on the target is on time), or when it has no response yet (first_response_at NULL).
For each priority that has tickets, return the columns priority, tickets, breached and breach_pct — 100 * breached / tickets rounded to 2 decimals. Return the rows in any order.
Tables
Table: Ticket
| Column | Type |
|---|---|
| ticket_id | int |
| priority | enum(urgent, high, normal) |
| created_at | datetime |
| first_response_at | datetime |
Primary key: ticket_id.
Times are whole minutes. first_response_at is the agent's first public reply, or NULL if nobody has replied.
Examples
Example 1
Ticket
| ticket_id | priority | created_at | first_response_at |
|---|---|---|---|
| 1 | urgent | 2025-08-04 10:00:00 | 2025-08-04 10:45:00 |
| 2 | urgent | 2025-08-04 11:00:00 | 2025-08-04 12:00:00 |
| 3 | urgent | 2025-08-04 12:00:00 | 2025-08-04 13:01:00 |
| 4 | high | 2025-08-04 09:00:00 | 2025-08-04 14:30:00 |
| 5 | high | 2025-08-04 09:30:00 | NULL |
| 6 | high | 2025-08-04 10:15:00 | 2025-08-04 11:00:00 |
| 7 | normal | 2025-08-03 18:00:00 | 2025-08-04 17:00:00 |
Output
| priority | tickets | breached | breach_pct |
|---|---|---|---|
| high | 3 | 2 | 66.67 |
| normal | 1 | 0 | 0 |
| urgent | 3 | 1 | 33.33 |
How to solve First-Response SLA Breach Rate by Priority
Split the work in two: first decide for each ticket whether it breached, then count per priority. The per-ticket flag is a CASE. A NULL first_response_at is a breach by definition and gets its own branch, because TIMESTAMPDIFF(MINUTE, created_at, NULL) is NULL and any comparison with it is unknown — without that branch the unanswered tickets would silently count as on time. Otherwise the wait in minutes is compared with the priority's target, which a nested CASE priority WHEN 'urgent' THEN 60 … supplies. The comparison is strict, so a reply exactly 60 minutes after an urgent ticket is on time.
With the flag in a CTE, the aggregation is plain: COUNT(*) tickets, SUM(breach) breaches, and their ratio times 100, rounded to 2 decimals — the rounding keeps MySQL's four-decimal division and other engines in agreement.
The first alternative keeps the targets as data, joining an inline table of (priority, target) rows — the better design once targets change per customer tier. The second counts the on-time replies and subtracts them from the total: an IF whose condition is NULL yields 0, so unanswered tickets drop out of the on-time count and land in the breaches without a separate test. All three are a single scan.
Reference solution (MySQL)
WITH flagged AS (
SELECT priority,
CASE
WHEN first_response_at IS NULL THEN 1
WHEN TIMESTAMPDIFF(MINUTE, created_at, first_response_at) >
CASE priority WHEN 'urgent' THEN 60 WHEN 'high' THEN 240 ELSE 1440 END THEN 1
ELSE 0
END AS breach
FROM Ticket
)
SELECT priority, COUNT(*) AS tickets, SUM(breach) AS breached,
ROUND(100 * SUM(breach) / COUNT(*), 2) AS breach_pct
FROM flagged
GROUP BY priorityAnother way
SELECT t.priority, COUNT(*) AS tickets,
SUM(CASE WHEN t.first_response_at IS NULL OR TIMESTAMPDIFF(MINUTE, t.created_at, t.first_response_at) > s.target_min THEN 1 ELSE 0 END) AS breached,
ROUND(100 * SUM(CASE WHEN t.first_response_at IS NULL OR TIMESTAMPDIFF(MINUTE, t.created_at, t.first_response_at) > s.target_min THEN 1 ELSE 0 END) / COUNT(*), 2) AS breach_pct
FROM Ticket t
JOIN (SELECT 'urgent' AS priority, 60 AS target_min UNION ALL SELECT 'high', 240 UNION ALL SELECT 'normal', 1440) s
ON s.priority = t.priority
GROUP BY t.priorityAnother way
SELECT priority, COUNT(*) AS tickets,
COUNT(*) - SUM(IF(TIMESTAMPDIFF(MINUTE, created_at, first_response_at) <= IF(priority = 'urgent', 60, IF(priority = 'high', 240, 1440)), 1, 0)) AS breached,
ROUND(100 * (COUNT(*) - SUM(IF(TIMESTAMPDIFF(MINUTE, created_at, first_response_at) <= IF(priority = 'urgent', 60, IF(priority = 'high', 240, 1440)), 1, 0))) / COUNT(*), 2) AS breach_pct
FROM Ticket GROUP BY priority← Label CSAT Survey Responses by Sentiment · Support Agents With a High Ticket Reopen Rate →