3. Who reports to Ben
Asked in screens shaped like: Google, Meta, Microsoft
The employees table is an org tree: manager_id points back at employee_id in the same table. Return Ben’s direct reports — one level down, not the whole subtree.
Requirements
- Columns: employee_id, name
- Only direct reports of the employee named Ben
- Order by employee_id
Expected output
employee_id, name
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
employees
~8 rows
Org tree. manager_id points back at employee_id in the same table.
- employee_idINTEGER
- nameTEXT
- manager_idINTEGER— NULL for the top of the tree
- deptTEXT— exec, eng, ops
- salaryREAL
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Join employees to itself with two aliases.
Hint 2
The child row’s manager_id equals the parent row’s employee_id.
Hint 3
Filter the manager alias on name = 'Ben'.
SELECT e.employee_id, e.name
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE m.name = 'Ben'
ORDER BY e.employee_id;Why it works
A self-join walks exactly one edge of the hierarchy. Getting every level below Ben would need a recursive CTE instead.
Run your query to see the result set.
Submit to run the checks.
Why it works
A self-join walks exactly one edge of the hierarchy. Getting every level below Ben would need a recursive CTE instead.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems