Self-Join

PySpark data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 12 minutes. Free to practice.

Self-join employees to resolve manager names. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

The employees table has employee_id, name and manager_id. The manager_id is the employee_id of the person's manager. To show the manager's name, you need to look up the same table again. Joining a table to itself is called a self-join. Make a second copy of the table that holds the managers, with employee_id renamed to manager_id and name renamed to manager_name. Left join the employees to this copy on manager_id. Return employee_id, name and manager_name, sorted by employee_id. The CEO has no manager (manager_id is NULL). The CEO must stay in the result, with manager_name empty. The editor ends with result = employees. Change that last line.

Requirements

• Build a second DataFrame of managers with columns manager_id and manager_name.
• Left join the employees to the managers on manager_id.
• Keep the CEO, with manager_name empty (None).
• Return employee_id, name, manager_name, sorted by employee_id, in result.

Constraints

• employees has 5 rows, so result has 5 rows.
• Use F.col(...).alias(...) to rename the columns of the manager copy so the names do not clash.
• The join must be a left join. An inner join would drop the CEO.

Examples

Input: org chart Output: employee_id | name | manager_name 1 | Ada | None 2 | Alan | Ada 3 | Grace | Ada 4 | Linus | Alan 5 | Ken | Alan Left self-join preserves the root manager row.

Topics: lakebench, pyspark, self-join.

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