Interview Questions/SQL/Employee Hierarchy Depth & Subordinate Salary

Employee Hierarchy Depth & Subordinate Salary

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Employee Hierarchy Depth & Subordinate Salary

You are given an Employees table where each employee may have a manager. Write a SQL query to find, for each employee: - Their depth in the org hierarchy (root = 0) - The total salary of ALL employees under them (direct + indirect reports only, excluding themselves) Return (id, name, depth, total_reports_salary) ordered by id. Table: Employees

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
manager_id	INT	NULL if top of hierarchy
salary	INT	Employee salary
department	VARCHAR	Department name

Examples

Example 1

Use a recursive CTE to traverse the hierarchy and compute depth. Use a second recursive CTE (subtree) to explode all descendants of every node. Subtract the root's own salary from the subtree total to get reports-only salary. Leaf nodes have 0.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql