Find Employees With No Direct Reports
Imagine a company's employee records stored in a database. Each employee has a unique ID, a name, and an optional ID of their direct manager. This manager relationship forms a hierarchy.
Your task is to find all employees who do not manage anyone directly.
- Employees table:
employee_id(integer, PK)name(varchar(100))manager_id(integer, FK to Employees.employee_id, NULLable)
Return the employee_id and name of these employees.
Handle ties by ordering by employee_id. NULLs and empty results are handled naturally by SQL. The order of the results matters: sort by employee_id.
Tables
CREATE TABLE Employees ( employee_id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INTEGER REFERENCES Employees(employee_id) );
Example data
INSERT INTO Employees (employee_id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 3), (6, 'Frank', NULL);
Your query is also checked on hidden datasets with different data.
Hints
Similar problems
- Find Employee's Manager NameMedium
- Top 3 Most Frequent Items per CustomerMedium
- Find Departments with Above Average Salary SpendingMedium
- Order Delivery Time CalculationMedium
- CTE: Top Employee Performance RankingMedium
- Internship Offers by Department and SalaryMedium
More in: SQL and Databases problems
-- Find employees with no direct reports
SELECT
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.