Agents Carrying More Than Their Team's Average Workload

Medium
335.

Agents Carrying More Than Their Team's Average Workload

To rebalance queues, the support manager compares every agent's open workload — tickets with status 'open' or 'pending' — with the average open workload of the agents in the same team. Every agent of the team counts in that average, including agents with no open ticket (they count as 0).

Return the agents whose open workload is strictly greater than their team's average, with the columns agent_id, agent_name, team and open_tickets. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent and the team they sit in.

Table: Ticket

ColumnTypeValues
ticket_idint
agent_idint
statusenumopen, pending, resolved, closed

Primary key: ticket_id.

agent_id is the assigned agent (always one of Agent).

Example

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

Agent

agent_idagent_nameteam
1AnanyaPayments
2DevPayments
3IraPayments
4NikhilBilling
5SimranBilling

Ticket

ticket_idagent_idstatus
11open
21pending
31open
42open
52resolved
63closed
74open
85pending

Output

agent_idagent_nameteamopen_tickets
1AnanyaPayments3
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.

Agents Carrying More Than Their Team's Average Workload

To rebalance queues, the support manager compares every agent's open workload — tickets with status 'open' or 'pending' — with the average open workload of…

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

Problem statement

To rebalance queues, the support manager compares every agent's open workload — tickets with status 'open' or 'pending' — with the average open workload of the agents in the same team. Every agent of the team counts in that average, including agents with no open ticket (they count as 0).

Return the agents whose open workload is strictly greater than their team's average, with the columns agent_id, agent_name, team and open_tickets. Return the rows in any order.

Tables

Table: Agent

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent and the team they sit in.

Table: Ticket

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

Primary key: ticket_id.

agent_id is the assigned agent (always one of Agent).

Examples

Example 1

Agent

agent_idagent_nameteam
1AnanyaPayments
2DevPayments
3IraPayments
4NikhilBilling
5SimranBilling

Ticket

ticket_idagent_idstatus
11open
21pending
31open
42open
52resolved
63closed
74open
85pending

Output

agent_idagent_nameteamopen_tickets
1AnanyaPayments3

How to solve Agents Carrying More Than Their Team's Average Workload

The comparison has two levels, so build them one at a time. The CTE computes each agent's open workload: a LEFT JOIN with the status test in the ON clause, then COUNT(t.ticket_id), so an agent with no open ticket still appears with 0. That matters, because those zeros are part of the team average — averaging over the tickets instead would leave idle agents out and inflate it.

With one row per agent, the team average is the AVG of open_tickets over the rows with the same team. A correlated subquery in the WHERE clause computes it for each agent's team and the comparison is strict, so an agent exactly on the average is not returned (a team where everyone carries the same load returns nobody).

A window does the same in one pass: AVG(COUNT(t.ticket_id)) OVER (PARTITION BY a.team) averages the grouped counts per team, which is legal because window functions run after GROUP BY. The third form keeps exact integers: an agent is above the average when open_tickets * agents_in_team > team_total, which avoids division altogether. All are linear in agents plus tickets with an index on Ticket.agent_id.

Reference solution (MySQL)

WITH workload AS (
  SELECT a.agent_id, a.agent_name, a.team, 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, a.team
)
SELECT w.agent_id, w.agent_name, w.team, w.open_tickets
FROM workload w
WHERE w.open_tickets > (SELECT AVG(x.open_tickets) FROM workload x WHERE x.team = w.team)

Another way

SELECT agent_id, agent_name, team, open_tickets
FROM (
  SELECT a.agent_id, a.agent_name, a.team, COUNT(t.ticket_id) AS open_tickets,
         AVG(COUNT(t.ticket_id)) OVER (PARTITION BY a.team) AS team_avg
  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, a.team
) s
WHERE open_tickets > team_avg

Another way

WITH workload AS (
  SELECT a.agent_id, a.agent_name, a.team,
         (SELECT COUNT(*) FROM Ticket t WHERE t.agent_id = a.agent_id AND t.status IN ('open', 'pending')) AS open_tickets
  FROM Agent a
), team_total AS (
  SELECT team, SUM(open_tickets) AS total, COUNT(*) AS agents FROM workload GROUP BY team
)
SELECT w.agent_id, w.agent_name, w.team, w.open_tickets
FROM workload w JOIN team_total tt ON tt.team = w.team
WHERE w.open_tickets * tt.agents > tt.total

← Monthly Change Failure Rate of Production Deploys · Customers Contacting Support Again Within a Week →