𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.
Find all employees who have the same manager.

Assume the table structure:
employees(employee_id, employee_name, manager_id)

𝗠𝗲: Challenge accepted! 💪

SELECT
e.employee_id,
e.employee_name,
e.manager_id,
m.employee_name AS manager_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.manager_id IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING COUNT(*) > 1
)
ORDER BY e.manager_id, e.employee_name;

💡 Explanation:
This query identifies managers who supervise more than one employee and returns all employees reporting to those managers.

✅ The subquery groups records by manager_id.

✅ HAVING COUNT(*) > 1 finds managers with multiple direct reports.

✅ A self join retrieves the manager's name.

✅ The outer query returns every employee reporting to those managers.

This question tests your understanding of:
✅ Self Joins
✅ GROUP BY and HAVING
✅ Subqueries
✅ Organizational Hierarchies

🎯 Expected Output Example
Employee Manager
John David
Alice David
Sarah Michael
Bob Michael

🚀 Alternative Using Window Functions
SELECT
employee_id,
employee_name,
manager_id
FROM (
SELECT
*,
COUNT(*) OVER (
PARTITION BY manager_id
) AS team_size
FROM employees
WHERE manager_id IS NOT NULL
) t
WHERE team_size > 1;

This approach uses a window function to count the number of employees under each manager without using GROUP BY.

🚀 Tip for SQL Job Seekers:
Hierarchy-based questions are very common in interviews. Practice problems involving:
Employees and managers
Parent-child relationships
Organizational charts
Category trees
Recursive queries WITH RECURSIVE or recursive CTEs

❤️ React with ❤️ for more SQL interview challenges!