Employee Hierarchy Queries | Dataford Interview Questions

Employee hierarchy with queries: build a structure over an employee hierarchy and answer queries, similar to tries / Design In-Memory File System.

Asked in the onsite stage. Write a SQL query that reports hierarchy statistics for every active employee.

Output

One row per active employee, including employees with no reports.
Columns: employee_id, employee_name, direct_reports, total_reports, and max_reporting_depth.
Sort by total_reports descending, then employee_id ascending.

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
job_title	VARCHAR(100)	Employee's job title
employment_status	VARCHAR(20)	Employment status, such as active or inactive

employee_hierarchy

Column	Type	Description
ancestor_id	INT	An employee with one or more descendants
descendant_id	INT	A direct or indirect report
depth	INT	Number of reporting levels between ancestor and descendant

Your question is Employee Hierarchy Queries. Start with the requirements and the two tables 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.