sqladvanced45 minutes

Design and Query a Multi-Level Employee Management Hierarchy

Create a relational data model to represent an organization's employee hierarchy with multiple reporting levels and implement complex SQL queries to retrieve hierarchical reports and aggregate statistics.

Challenge prompt

You are tasked with designing a database schema to represent employees in a company, where each employee may report to one manager, and managers can themselves report to higher-level managers, forming a multi-level hierarchy. Build the necessary tables and write SQL queries to achieve the following: 1. Create a table named `Employees` with columns `employee_id` (primary key), `name`, `position`, `manager_id` (foreign key referencing `employee_id`, nullable). 2. Insert sample data representing at least 10 employees spanning at least 3 hierarchical levels. 3. Write a recursive SQL query (using Common Table Expressions) to fetch the full hierarchy tree starting from the top-level manager (CEO) down to all subordinates, showing their levels in the hierarchy. 4. Write a query to calculate the total number of direct and indirect reports for each manager. Deliverables: - The `CREATE TABLE` statement for `Employees`. - Sample `INSERT` statements for your data. - SQL query for fetching the full employee hierarchy with levels. - SQL query for counting total reports per manager.

Guidance

• Use a self-referencing foreign key for `manager_id` to model the hierarchy in a single table.
• Leverage recursive Common Table Expressions (CTEs) to traverse multiple levels of the hierarchy.
• Aggregate results carefully to count all subordinates (direct and indirect) per manager.

Hints

• Make sure the `manager_id` column allows NULLs to accommodate the top-level manager who has no manager.
• In your recursive CTE, start from employees with NULL `manager_id` to get the root of the hierarchy.
• Use `COUNT` combined with `GROUP BY` to calculate the number of subordinates per manager.

Starter code

CREATE TABLE Employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  position VARCHAR(100),
  manager_id INT NULL,
  FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
);

-- Insert sample data here

Expected output

For the hierarchical query: employee_id | name | position | level ------------------------------------------------- 1 | Alice | CEO | 1 2 | Bob | VP Engineering | 2 3 | Carol | VP Marketing | 2 4 | Dave | Engineering Mgr| 3 5 | Eve | Marketing Mgr | 3 ... (remaining employees) For the reports count query: manager_id | total_reports ------------------------- 1 | 9 2 | 4 3 | 2 4 | 1 5 | 0 ... (remaining managers)

Core concepts

recursive Common Table Expressionsself-referencing foreign keyshierarchical data modelingSQL aggregation

Challenge a Friend

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