Support Agents With a High Ticket Reopen Rate

Medium
332.

Support Agents With a High Ticket Reopen Rate

MediumJoinsAggregation

MySQL

A ticket is reopened when the customer replies after it was resolved; the help desk logs that as a 'reopened' event, and a ticket can be reopened more than once. Quality reviews look at each agent's resolved tickets (resolved_at not NULL) and how many of them were reopened at least once. A reopened ticket that is still unresolved is not one of the agent's resolved tickets.

Return the agents whose reopen rate is at least 25%, with the columns agent_id, agent_name, resolved_tickets, reopened_tickets and reopen_pct (100 * reopened_tickets / resolved_tickets, rounded to 2 decimals). Agents with no resolved ticket are left out. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar

Primary key: agent_id.

One row per support agent.

Table: Ticket

ColumnType
ticket_idint
agent_idint
resolved_atdatetime

Primary key: ticket_id.

Each ticket is owned by one agent. resolved_at is the latest resolution, or NULL while the ticket is open.

Table: TicketEvent

ColumnTypeValues
event_idint
ticket_idint
event_typeenumcreated, commented, resolved, reopened
event_atdatetime

Primary key: event_id.

The ticket's history, one row per event.

Example

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

Agent

agent_idagent_name
1Meera
2Karan
3Tanvi

Ticket

ticket_idagent_idresolved_at
10112025-09-02 12:00:00
10212025-09-03 15:00:00
10312025-09-04 10:00:00
10422025-09-02 18:00:00
1052NULL
10632025-09-05 11:00:00
10732025-09-05 16:00:00

TicketEvent

event_idticket_idevent_typeevent_at
1101resolved2025-09-01 12:00:00
2101reopened2025-09-01 20:00:00
3101reopened2025-09-02 09:00:00
4101resolved2025-09-02 12:00:00
5104commented2025-09-02 14:00:00
6105reopened2025-09-03 10:00:00
7106reopened2025-09-05 09:00:00
8107resolved2025-09-05 16:00:00

Output

agent_idagent_nameresolved_ticketsreopened_ticketsreopen_pct
1Meera3133.33
3Tanvi2150
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.

Support Agents With a High Ticket Reopen Rate — SQL Problem

A ticket is reopened when the customer replies after it was resolved; the help desk logs that as a 'reopened' event, and a ticket can be reopened more than…

  • Difficulty: Medium
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #332

Problem statement

A ticket is reopened when the customer replies after it was resolved; the help desk logs that as a 'reopened' event, and a ticket can be reopened more than once. Quality reviews look at each agent's resolved tickets (resolved_at not NULL) and how many of them were reopened at least once. A reopened ticket that is still unresolved is not one of the agent's resolved tickets.

Return the agents whose reopen rate is at least 25%, with the columns agent_id, agent_name, resolved_tickets, reopened_tickets and reopen_pct (100 * reopened_tickets / resolved_tickets, rounded to 2 decimals). Agents with no resolved ticket are left out. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar

Primary key: agent_id.

One row per support agent.

Table: Ticket

ColumnType
ticket_idint
agent_idint
resolved_atdatetime

Primary key: ticket_id.

Each ticket is owned by one agent. resolved_at is the latest resolution, or NULL while the ticket is open.

Table: TicketEvent

ColumnType
event_idint
ticket_idint
event_typeenum(created, commented, resolved, reopened)
event_atdatetime

Primary key: event_id.

The ticket's history, one row per event.

Examples

Example 1

Agent

agent_idagent_name
1Meera
2Karan
3Tanvi

Ticket

ticket_idagent_idresolved_at
10112025-09-02 12:00:00
10212025-09-03 15:00:00
10312025-09-04 10:00:00
10422025-09-02 18:00:00
1052NULL
10632025-09-05 11:00:00
10732025-09-05 16:00:00

TicketEvent

event_idticket_idevent_typeevent_at
1101resolved2025-09-01 12:00:00
2101reopened2025-09-01 20:00:00
3101reopened2025-09-02 09:00:00
4101resolved2025-09-02 12:00:00
5104commented2025-09-02 14:00:00
6105reopened2025-09-03 10:00:00
7106reopened2025-09-05 09:00:00
8107resolved2025-09-05 16:00:00

Output

agent_idagent_nameresolved_ticketsreopened_ticketsreopen_pct
1Meera3133.33
3Tanvi2150

How to solve Support Agents With a High Ticket Reopen Rate

The rate is per resolved ticket, so the first step reduces the event log to one fact per ticket: was there any 'reopened' event? An EXISTS inside a CASE gives a 0/1 flag per resolved ticket, which is immune to tickets reopened several times — joining TicketEvent directly would produce one row per reopen and inflate both counts.

Then group by agent: COUNT(*) is the resolved tickets, SUM(was_reopened) the reopened ones, and the HAVING keeps the agents at 25% or more. Writing the threshold as 100 * reopened >= 25 * resolved compares integers, so an agent sitting exactly on 25% is kept with no floating-point doubt; the reported percentage is rounded to 2 decimals. Unresolved tickets are filtered out before grouping, so a reopened ticket that is still open counts for nobody, and an agent with no resolved tickets has no rows at all and is left out.

The alternative does the LEFT JOIN to the reopen events and repairs the multiplication with COUNT(DISTINCT …): distinct resolved tickets, and distinct tickets that matched a reopen. It is correct but makes the database build and de-duplicate the larger join; the EXISTS form stops at the first reopen per ticket.

Reference solution (MySQL)

WITH resolved AS (
  SELECT t.agent_id,
         CASE WHEN EXISTS (SELECT 1 FROM TicketEvent e
                           WHERE e.ticket_id = t.ticket_id AND e.event_type = 'reopened')
              THEN 1 ELSE 0 END AS was_reopened
  FROM Ticket t
  WHERE t.resolved_at IS NOT NULL
)
SELECT a.agent_id, a.agent_name,
       COUNT(*) AS resolved_tickets,
       SUM(r.was_reopened) AS reopened_tickets,
       ROUND(100 * SUM(r.was_reopened) / COUNT(*), 2) AS reopen_pct
FROM resolved r
JOIN Agent a ON a.agent_id = r.agent_id
GROUP BY a.agent_id, a.agent_name
HAVING 100 * SUM(r.was_reopened) >= 25 * COUNT(*)

Another way

SELECT a.agent_id, a.agent_name,
       COUNT(DISTINCT t.ticket_id) AS resolved_tickets,
       COUNT(DISTINCT e.ticket_id) AS reopened_tickets,
       ROUND(100 * COUNT(DISTINCT e.ticket_id) / COUNT(DISTINCT t.ticket_id), 2) AS reopen_pct
FROM Agent a
JOIN Ticket t ON t.agent_id = a.agent_id AND t.resolved_at IS NOT NULL
LEFT JOIN TicketEvent e ON e.ticket_id = t.ticket_id AND e.event_type = 'reopened'
GROUP BY a.agent_id, a.agent_name
HAVING COUNT(DISTINCT e.ticket_id) * 4 >= COUNT(DISTINCT t.ticket_id)

← First-Response SLA Breach Rate by Priority · Slowest Endpoint in Each Service →