First-Response SLA Breach Rate by Priority

Medium
331.

First-Response SLA Breach Rate by Priority

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

ColumnTypeValues
ticket_idint
priorityenumurgent, high, normal
created_atdatetime
first_response_atdatetime

Primary key: ticket_id.

Times are whole minutes. first_response_at is the agent's first public reply, or NULL if nobody has replied.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Ticket

ticket_idprioritycreated_atfirst_response_at
1urgent2025-08-04 10:00:002025-08-04 10:45:00
2urgent2025-08-04 11:00:002025-08-04 12:00:00
3urgent2025-08-04 12:00:002025-08-04 13:01:00
4high2025-08-04 09:00:002025-08-04 14:30:00
5high2025-08-04 09:30:00NULL
6high2025-08-04 10:15:002025-08-04 11:00:00
7normal2025-08-03 18:00:002025-08-04 17:00:00

Output

priorityticketsbreachedbreach_pct
high3266.67
normal100
urgent3133.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ticket_idint
priorityenum(urgent, high, normal)
created_atdatetime
first_response_atdatetime

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_idprioritycreated_atfirst_response_at
1urgent2025-08-04 10:00:002025-08-04 10:45:00
2urgent2025-08-04 11:00:002025-08-04 12:00:00
3urgent2025-08-04 12:00:002025-08-04 13:01:00
4high2025-08-04 09:00:002025-08-04 14:30:00
5high2025-08-04 09:30:00NULL
6high2025-08-04 10:15:002025-08-04 11:00:00
7normal2025-08-03 18:00:002025-08-04 17:00:00

Output

priorityticketsbreachedbreach_pct
high3266.67
normal100
urgent3133.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 priority

Another 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.priority

Another 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 →