SQL Self Joins and Explode | Dataford Interview Questions

Write SQL and PySpark transformations using self-joins, split, explode, and group-by aggregations. Use the employees table and include only active direct reports with non-empty tags.

Output

One row per manager and tag, with manager_id, manager_name, tag, direct_report_count, and total_salary.
Include unmatched manager IDs as Unknown manager.
Order by manager_id, then normalized tag.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
manager_id	INT	Employee ID of the direct manager
skill_tags	VARCHAR(255)	Comma-separated employee skill tags
annual_salary	DECIMAL(12,2)	Annual employee salary
is_active	BOOLEAN	Whether the employee is active

Your question is SQL Self Joins and Explode. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write SQL and PySpark transformations using self-joins, split, explode, and group-by aggregations. Use the employees table and include only active direct reports with non-empty tags.

Output

One row per manager and tag, with manager_id, manager_name, tag, direct_report_count, and total_salary.
Include unmatched manager IDs as Unknown manager.
Order by manager_id, then normalized tag.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
manager_id	INT	Employee ID of the direct manager
skill_tags	VARCHAR(255)	Comma-separated employee skill tags
annual_salary	DECIMAL(12,2)	Annual employee salary
is_active	BOOLEAN	Whether the employee is active

Your question is SQL Self Joins and Explode. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.