Hierarchy Questions and answers

 

---

📌 Table: emp_details

id	name	manager_id	designation
1	shripadh	NULL	ceo
2	satya	5	software engineer
3	jia	5	data analyst
4	david	5	data scientist
5	michael	7	manager
6	aravind	7	architect
7	asha	1	cto
8	mariyam	1	manager
9	reshma	8	business analyst
10	akshay	8	java developer

---

✅ 1. Top-Down Hierarchy (CEO → All Employees)

👉 MOST IMPORTANT QUERY

WITH emp_hierarchy AS (
    SELECT 
        id,
        name,
        manager_id,
        designation,
        0 AS level
    FROM emp_details
    WHERE manager_id IS NULL   -- CEO

    UNION ALL

    SELECT 
        e.id,
        e.name,
        e.manager_id,
        e.designation,
        h.level + 1
    FROM emp_details e
    JOIN emp_hierarchy h
        ON e.manager_id = h.id
)
SELECT *
FROM emp_hierarchy
ORDER BY level, manager_id;

🔹 Output (Hierarchy Level)

id	name	designation	level
1	shripadh	ceo	0
7	asha	cto	1
8	mariyam	manager	1
5	michael	manager	2
6	aravind	architect	2
9	reshma	business analyst	2
10	akshay	java developer	2
2	satya	software engineer	3
3	jia	data analyst	3
4	david	data scientist	3

---

✅ 2. Employee → Manager Chain (Bottom-Up)

👉 “Show reporting chain for an employee”

WITH reporting_chain AS (
    SELECT 
        id,
        name,
        manager_id,
        designation,
        0 AS level
    FROM emp_details
    WHERE name = 'satya'

    UNION ALL

    SELECT 
        e.id,
        e.name,
        e.manager_id,
        e.designation,
        rc.level + 1
    FROM emp_details e
    JOIN reporting_chain rc
        ON rc.manager_id = e.id
)
SELECT *
FROM reporting_chain;

🔹 Output

name	designation	level
satya	software engineer	0
michael	manager	1
asha	cto	2
shripadh	ceo	3

---

✅ 3. Hierarchy with Path (Org Tree Path)

👉 Very popular senior-level question

WITH emp_path AS (
    SELECT 
        id,
        name,
        manager_id,
        CAST(name AS VARCHAR(500)) AS hierarchy_path
    FROM emp_details
    WHERE manager_id IS NULL

    UNION ALL

    SELECT 
        e.id,
        e.name,
        e.manager_id,
        CAST(p.hierarchy_path + ' -> ' + e.name AS VARCHAR(500))
    FROM emp_details e
    JOIN emp_path p
        ON e.manager_id = p.id
)
SELECT *
FROM emp_path;

🔹 Output

shripadh
shripadh -> asha
shripadh -> mariyam
shripadh -> asha -> michael
shripadh -> asha -> aravind
shripadh -> mariyam -> reshma
shripadh -> mariyam -> akshay
shripadh -> asha -> michael -> satya
shripadh -> asha -> michael -> jia
shripadh -> asha -> michael -> david

---

✅ 4. Find Direct Reports of a Manager

👉 “Who reports to Asha?”

SELECT *
FROM emp_details
WHERE manager_id = (
    SELECT id FROM emp_details WHERE name = 'asha'
);

---

✅ 5. Find All Subordinates Under a Manager

👉 “All employees under Asha (any level)”

WITH subordinates AS (
    SELECT id, name, manager_id
    FROM emp_details
    WHERE name = 'asha'

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM emp_details e
    JOIN subordinates s
        ON e.manager_id = s.id
)
SELECT *
FROM subordinates
WHERE name <> 'asha';

---

✅ 6. Count Employees Under Each Manager

👉 Very common business question

WITH emp_tree AS (
    SELECT id, manager_id
    FROM emp_details
    WHERE manager_id IS NOT NULL

    UNION ALL

    SELECT e.id, e.manager_id
    FROM emp_details e
    JOIN emp_tree t
        ON e.manager_id = t.id
)
SELECT manager_id, COUNT(*) AS total_reportees
FROM emp_tree
GROUP BY manager_id;

---

✅ 7. Leaf Employees (No Reportees)

👉 “Who are individual contributors?”

SELECT *
FROM emp_details e
WHERE NOT EXISTS (
    SELECT 1
    FROM emp_details x
    WHERE x.manager_id = e.id
);

---

🧠 INTERVIEW SUMMARY (MUST SAY THIS)

“I use recursive CTEs for hierarchical data to support both top-down and bottom-up traversal, including levels and path generation.”