Interview Question Bank · Self LEFT JOIN · COUNT(column) · filter in ON
2. Span of control, zero included
EasyHRQuestion #182
Concept: Self LEFT JOIN · COUNT(column) · filter in ONshow
To count each manager's reports, join the employees table to itself: one copy plays the manager, the other the report. A LEFT JOIN keeps managers with nobody under them, and COUNT(r.id) counts only matched reports, so they get 0 (COUNT(*) would count the empty row as 1). A condition on the report side belongs in the ON clause; in WHERE it throws away the very rows the LEFT JOIN kept.
Example (different data):
SELECT t.name, COUNT(s.id) AS open_tasks FROM teams t LEFT JOIN tasks s ON s.team_id = t.id AND s.done = false GROUP BY t.id, t.name;
Your task
Quillmoor's CEO suspects some managers are stretched thin while others have nobody left to manage, and asks People Ops for a span-of-control list.
For every people manager (is_manager is true), return name, title and direct_reports (current employees whose manager_id is theirs, 0 if none). Sort by direct_reports descending, then name.
Starting Postgres in your browser…
Checking your access…