Agents hired before their manager

Some agents have been at the help desk longer than the person they report to. HR wants the list.

In agents, manager_id points at another row in the same table. Managers themselves have a NULL manager_id and should not appear in the output.

An agent counts if their hired_date is strictly earlier than their manager’s. Hired on the same day doesn’t count.

Return one row per such agent:

column	type	meaning
agent_id	integer	the agent
agent_name	text	the agent’s name
manager_name	text	their manager’s name

Row order doesn’t matter.

Tables: agents.

Approach

Self-join: give agents two aliases, one for the agent and one for the manager, and compare their hire dates.

SELECT a.agent_id,
       a.name AS agent_name,
       m.name AS manager_name
FROM agents a
JOIN agents m ON m.agent_id = a.manager_id
WHERE a.hired_date < m.hired_date;

Why it works

A join doesn’t care that both sides are the same table. The aliases make two independent row streams, and the ON clause pairs each agent with exactly one manager row.

Managers drop out on their own: NULL = anything is never true, so an inner join finds no partner for them. < rather than <= leaves out agents hired on the same day as their manager.

Under the hood

DuckDB reads agents twice, once per alias. It builds a hash table on m.agent_id (the primary key, so one row per key) and probes it with each agent’s manager_id. Rows with a NULL key skip the probe entirely. With 20 rows it all fits in a cache line or two; the same plan works for 20 million.