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
| Column | Type |
|---|---|
| agent_id | int |
| agent_name | varchar |
| team | varchar |
Primary key: agent_id.
One row per support agent and the team they sit in.
Table: Ticket
| Column | Type |
|---|---|
| ticket_id | int |
| agent_id | int |
| status | enum(open, pending, resolved, closed) |
Primary key: ticket_id.
agent_id is the assigned agent (always one of Agent).
Examples
Example 1
Agent
| agent_id | agent_name | team |
|---|---|---|
| 1 | Ananya | Payments |
| 2 | Dev | Payments |
| 3 | Ira | Payments |
| 4 | Nikhil | Billing |
| 5 | Simran | Billing |
Ticket
| ticket_id | agent_id | status |
|---|---|---|
| 1 | 1 | open |
| 2 | 1 | pending |
| 3 | 1 | open |
| 4 | 2 | open |
| 5 | 2 | resolved |
| 6 | 3 | closed |
| 7 | 4 | open |
| 8 | 5 | pending |
Output
| agent_id | agent_name | team | open_tickets |
|---|---|---|---|
| 1 | Ananya | Payments | 3 |
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_avgAnother 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 →