Drill 2 of 12 in HR questions
0

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…