Org depth without a recursive CTE
PySpark data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 18 minutes. Part of the Pro drill bank.
Calculate reporting hierarchy depth for employees in a bounded-depth organizational chart using self-joins. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
Given a DataFrame hierarchy with columns emp_id, name, and mgr_id (where the CEO has mgr_id as null): The maximum reporting depth in the organization is bounded at 4 levels. Compute each employee's depth in the reporting tree: CEO has depth = 0 Direct reports of CEO have depth = 1 Reports of direct reports have depth = 2 Level 4 reports have depth = 3 Columns to return: emp_id, name, depth. Sort by emp_id ascending. Assign the final DataFrame to result.
Requirements
- CEO depth is 0.
- Columns must be emp_id, name, depth.
Constraints
- Hierarchy depth is at most 4.
Examples
Input: hierarchy emp_id | name | mgr_id 1 | CEO | null 2 | VP | 1 3 | Dir | 2 4 | Mgr | 3 Output: emp_id | name | depth 1 | CEO | 0 2 | VP | 1 3 | Dir | 2 4 | Mgr | 3 CEO has no manager (0), VP reports to CEO (1), Director reports to VP (2), and Manager reports to Director (3).
Topics: lakebench, pyspark, fixed-depth, rollup.
More PySpark interview questions · All interview problems · Learn data engineering
Org depth without a recursive CTE
Interview-style drill: Calculate reporting hierarchy depth for employees in a bounded-depth organizational chart using self-joins.
Given a DataFrame `hierarchy` with columns `emp_id`, `name`, and `mgr_id` (where the CEO has `mgr_id` as null): The maximum reporting depth in the organization is bounded at 4 levels. Compute each employee's depth in the reporting tree: - CEO has `depth = 0` - Direct reports of CEO have `depth = 1` - Reports of direct reports have `depth = 2` - Level 4 reports have `depth = 3` Columns to return: `emp_id`, `name`, `depth`. Sort by `emp_id` ascending. Assign the final DataFrame to `result`.