sqladvanced30 minutes

Design and Query an Employee Hierarchy Data Model

Create a normalized data model to represent an employee hierarchy within a company, then write an advanced SQL query to retrieve hierarchical reports and aggregated information.

Challenge prompt

Your company wants to maintain an employee organizational chart with multiple levels of management. Design a SQL data model that supports: employees, their managers (who are also employees), departments, and salaries. Each employee belongs to exactly one department and has a single manager except for the CEO, who has none. Then write an efficient SQL query that returns each employee's name, their manager's name, their department, and the total count of direct and indirect reports under them (i.e., total employees in their hierarchy tree). Make sure your design supports fast hierarchical queries and avoids data redundancy.

Guidance

• Use a self-referencing table for employees to represent manager relationships.
• Ensure proper normalization by separating departments into their own table.
• Use recursive Common Table Expressions (CTEs) to calculate hierarchical counts efficiently.
• Optimize indexes on manager foreign keys for recursive queries.

Hints

• Use a 'manager_id' column in the employees table referencing the employee's own ID.
• The CEO's 'manager_id' can be NULL to indicate no manager.
• Leverage recursive CTEs with UNION ALL to traverse the entire report tree.

Starter code

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(100) NOT NULL,
  manager_id INT REFERENCES employees(employee_id),
  department_id INT REFERENCES departments(department_id),
  salary DECIMAL(10, 2) NOT NULL
);

Expected output

A result set with columns: employee_name, manager_name, department_name, total_reports_count - where total_reports_count indicates the number of all employees reporting directly or indirectly under that employee.

Core concepts

recursive CTEself-referencing foreign keydata normalizationhierarchical queries

Challenge a Friend

Send this duel to someone else and see if they can solve it.