Coding Interview Preparation on Telegram: “📊 SQL SATURDAY #6

📊 SQL SATURDAY #6 - Self Joins and Hierarchies


employees
+----+---------+-----------+
| id | name    | manager_id|
+----+---------+-----------+
| 1  | Alice   | NULL      |
| 2  | Bob     | 1         |
| 3  | Charlie | 1         |
| 4  | Diana   | 2         |

Question: List each employee alongside their manager's name.

sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This is a self join - the same table joined to itself, aliased twice so SQL can treat it as two logical tables. Alice (the CEO) has manager_id = NULL, so she shows up with manager = NULL thanks to the LEFT JOIN.

⚠️ Interview follow-up: "Find all employees who earn more than their manager." This is a classic self-join + comparison question:

sql
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Deeper follow-up: "Find the full management chain for a given employee, regardless of depth." A single self join can't do this - it only goes one level up. This needs a recursive CTE:

sql
WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 4  -- start with Diana

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

Recursive CTEs come up more than people expect for org charts, category trees, and dependency graphs. Worth practicing even if it feels unfamiliar at first.

Have you ever had to query an actual org chart or category tree at work? 👇