Team Leads With Five Agents

A call centre stores every agent in one table, and each row points to the agent's direct team lead in the same table. Operations wants to know which people lead at least five agents directly. Agents further down the chain do not count.

Return agent_id and agent_name of each such lead, in any order. A lead id that does not belong to any agent in the table must not appear. Names are not unique, so two different leads can share a name. Return no rows if nobody qualifies.

Tables

call_agents — One row per person working in the call centre.

Example

call_agents

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 26 of 70 · SQLite

The idea

The table holds both leads and the agents they lead, so join it to itself. Call one copy lead and the other member, and match member.lead_id = lead.agent_id. Each joined row is one direct reporting line. Agents two levels down point to their own lead, not to the higher one, so they are never counted.

Group by the lead and keep groups with HAVING COUNT(*) >= 5. Group by agent_id, not the name, because names repeat and grouping by name would merge two separate teams.

The inner join also handles stale data. If a lead_id points to someone who has left, nothing matches on the lead side and that id cannot appear. The alternative counts reports per lead_id in a subquery first, then keeps agents whose id is in that list. That makes it clear the answer is a set of existing people.

Solution

SELECT lead.agent_id,
       lead.agent_name
FROM call_agents AS lead
JOIN call_agents AS member
  ON member.lead_id = lead.agent_id
GROUP BY lead.agent_id, lead.agent_name
HAVING COUNT(*) >= 5;

Another way

SELECT agent_id,
       agent_name
FROM call_agents
WHERE agent_id IN (
  SELECT lead_id
  FROM call_agents
  WHERE lead_id IS NOT NULL
  GROUP BY lead_id
  HAVING COUNT(*) >= 5
);

Why common answers fail

Groups by name, so two leads who share a name have their teams added together.

SELECT lead.agent_id, lead.agent_name
FROM call_agents AS lead
JOIN call_agents AS member ON member.lead_id = lead.agent_id
GROUP BY lead.agent_name
HAVING COUNT(*) >= 5;

Uses > 5, so a lead with exactly five agents is missed.

SELECT lead.agent_id, lead.agent_name
FROM call_agents AS lead
JOIN call_agents AS member ON member.lead_id = lead.agent_id
GROUP BY lead.agent_id, lead.agent_name
HAVING COUNT(*) > 5;

Groups the members without looking up the lead, so it returns a member's name and keeps lead ids that are not in the table.

SELECT lead_id AS agent_id, MAX(agent_name) AS agent_name
FROM call_agents
WHERE lead_id IS NOT NULL
GROUP BY lead_id
HAVING COUNT(*) >= 5;

What the interviewer is checking

A classic hierarchy question: can you read a self-referencing table, aggregate by the right key, and filter groups with HAVING instead of WHERE?

Mistakes to avoid

• Filtering COUNT(*) in WHERE instead of HAVING (an error).
• Grouping by the lead name instead of the lead id.
• Returning lead_id values directly, including ids of people who have left.

Follow-ups to expect

• Count indirect reports too (a recursive CTE walking down the tree).
• Also return the team size (select COUNT(*) AS team_size).
• List leads with no direct reports at all (anti-join: NOT EXISTS a member).

In real work

Org-chart analytics such as span-of-control reviews, and any parent/child table like categories or referral trees, use this self-join and HAVING pattern.

Practice next