Employee and manager names

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Self Joins. About 12 minutes. Part of the Pro drill bank.

In the employees table each person has a manager_id, which is the employee_id of their boss. So the boss is in the same table. To show the boss's name next to the employee's name, you have to join the table to itself. This is called a self-join. Return every employee who has a manager, with 2 columns: employee_name and manager_name. Sort by employee_name.

Requirements

• Join employees to itself, matching an employee's manager_id to the manager's employee_id.
• Return only employees who have a manager.
• Name the columns employee_name and manager_name.
• Sort by employee_name.

Constraints

• The table is employees, used twice with two different aliases, for example e for the employee and m for the manager.
• Return only these 2 columns, in this order: employee_name, manager_name.
• The person at the top has no manager (manager_id is NULL) and must not appear.

Examples

Input: employees employee_id | name | manager_id 1 | Alice Chen | NULL 2 | Bob Kumar | 1 4 | Eve Ng | 2 5 | Omar Ali | 1 Output: employee_name | manager_name Bob Kumar | Alice Chen Eve Ng | Bob Kumar Omar Ali | Alice Chen Why this passes: Alice has no manager_id so the join drops her.

Topics: lakebench, sql, self-join.

More SQL interview questions · All interview problems · Learn data engineering