Open Ticket Load per Support Agent

Easy
324.

Open Ticket Load per Support Agent

Before assigning new tickets, the support manager wants to see how many tickets each agent is still working on — tickets whose status is 'open' or 'pending'. Resolved and closed tickets are finished work and do not count, and unassigned tickets (agent_id NULL) belong to nobody.

Return every agent with the columns agent_id, agent_name and open_tickets; an agent with no open or pending ticket gets 0. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent.

Table: Ticket

ColumnTypeValues
ticket_idint
agent_idint
statusenumopen, pending, resolved, closed

Primary key: ticket_id.

agent_id is the assigned agent, or NULL while the ticket sits in the unassigned queue.

Example

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

Agent

agent_idagent_nameteam
1PriyaPayments
2KabirPayments
3SnehaBilling
4ArjunBilling

Ticket

ticket_idagent_idstatus
5011open
5021pending
5031resolved
5042closed
5053open
506NULLopen
5072resolved

Output

agent_idagent_nameopen_tickets
1Priya2
2Kabir0
3Sneha1
4Arjun0
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.

Open Ticket Load per Support Agent — SQL Joins Problem

Before assigning new tickets, the support manager wants to see how many tickets each agent is still working on — tickets whose status is 'open' or 'pending'.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #324

Problem statement

Before assigning new tickets, the support manager wants to see how many tickets each agent is still working on — tickets whose status is 'open' or 'pending'. Resolved and closed tickets are finished work and do not count, and unassigned tickets (agent_id NULL) belong to nobody.

Return every agent with the columns agent_id, agent_name and open_tickets; an agent with no open or pending ticket gets 0. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent.

Table: Ticket

ColumnType
ticket_idint
agent_idint
statusenum(open, pending, resolved, closed)

Primary key: ticket_id.

agent_id is the assigned agent, or NULL while the ticket sits in the unassigned queue.

Examples

Example 1

Agent

agent_idagent_nameteam
1PriyaPayments
2KabirPayments
3SnehaBilling
4ArjunBilling

Ticket

ticket_idagent_idstatus
5011open
5021pending
5031resolved
5042closed
5053open
506NULLopen
5072resolved

Output

agent_idagent_nameopen_tickets
1Priya2
2Kabir0
3Sneha1
4Arjun0

How to solve Open Ticket Load per Support Agent

The answer has one row per agent, so the query starts from Agent and LEFT JOINs the tickets: an agent with no matching ticket still appears once, with NULL in every ticket column.

The subtle part is the status filter. Put t.status IN ('open', 'pending') in the WHERE clause and it runs after the join — on the NULL rows of agents without tickets, and on agents whose tickets are all resolved, the condition is not true and those agents vanish, so they never show their 0. Inside the ON clause the condition only decides which tickets match, and the agent row survives regardless.

COUNT(t.ticket_id) then counts the matched tickets and gives 0 for an agent whose only row is the NULL one — COUNT(*) would say 1. Unassigned tickets have agent_id NULL, which equals no agent, so they are ignored without extra work. A correlated subquery per agent, or a conditional SUM over an unfiltered LEFT JOIN wrapped in COALESCE, gives the same numbers; all three are one pass with an index on Ticket.agent_id.

Reference solution (MySQL)

SELECT a.agent_id, a.agent_name, COUNT(t.ticket_id) AS open_tickets
FROM Agent a
LEFT JOIN Ticket t
  ON t.agent_id = a.agent_id AND t.status IN ('open', 'pending')
GROUP BY a.agent_id, a.agent_name

Another way

SELECT a.agent_id, a.agent_name, (SELECT COUNT(*) FROM Ticket t WHERE t.agent_id = a.agent_id AND t.status IN ('open', 'pending')) AS open_tickets FROM Agent a

Another way

SELECT a.agent_id, a.agent_name,
       COALESCE(SUM(CASE WHEN t.status IN ('open', 'pending') THEN 1 ELSE 0 END), 0) AS open_tickets
FROM Agent a LEFT JOIN Ticket t ON t.agent_id = a.agent_id
GROUP BY a.agent_id, a.agent_name

← Urgent Support Tickets Waiting More Than a Day · Hosts That Hit CPU Saturation →