Employee Hierarchy Depth & Subordinate Salary

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

Medium

Description

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

Database Schema (Inferred)

Employees

Column Name	Example Value
id	1
name	Alice
manager_id	NULL
salary	10000
department	Engineering

Example

Employees

id	name	manager_id	salary	department
1	Alice	NULL	10000	Engineering
2	Bob	1	8000	Engineering
3	Carol	1	7000	Engineering
4	Dave	2	5000	Engineering
5	Eve	2	4500	Engineering
6	Frank	3	6000	Engineering

Output

id	name	depth	total_reports_salary
1	Alice	0	30500
2	Bob	1	9500
3	Carol	1	6000
4	Dave	2	0
5	Eve	2	0
6	Frank	2	0

Explanation:

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.