Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine0%

Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Intermediate20 min read•Updated: 2026-09-12

Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Capstone Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Enterprise organizations are complex structures containing multi-tier managerial hierarchies, cross-departmental budgets, and salary grades.

In this capstone, you will construct a corporate data warehouse schema and deploy an Analytics Engine combining Recursive Common Table Expressions, Window Functions, and Multi-Table Aggregations to extract executive insights.

1. Database Schema & Architecture

Our schema models corporate organizational charts:

departments: Department names, locations, and annual budget caps.
employees: Staff records containing salaries, department associations, and a self-referencing manager_id.
performance_reviews: Quarterly performance scores (1.0 to 5.0) evaluated over time.

1CREATE DATABASE IF NOT EXISTS corporate_analytics_db;
2USE corporate_analytics_db;
3 
4-- 1. Departments
5CREATE TABLE departments (
6    dept_id INT AUTO_INCREMENT PRIMARY KEY,
7    dept_name VARCHAR(50) NOT NULL UNIQUE,
8    location VARCHAR(50) NOT NULL,
9    annual_budget DECIMAL(15, 2) NOT NULL
10) ENGINE = InnoDB;
11 
12-- 2. Employees with Hierarchical Self-Reference
13CREATE TABLE employees (
14    emp_id INT AUTO_INCREMENT PRIMARY KEY,
15    dept_id INT NOT NULL,
16    manager_id INT NULL,
17    first_name VARCHAR(50) NOT NULL,
18    last_name VARCHAR(50) NOT NULL,
19    job_title VARCHAR(50) NOT NULL,
20    salary DECIMAL(12, 2) NOT NULL,
21    hire_date DATE NOT NULL,
22    CONSTRAINT fk_emp_dept 
23        FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
24    CONSTRAINT fk_emp_manager 
25        FOREIGN KEY (manager_id) REFERENCES employees(emp_id) 
26        ON DELETE SET NULL
27) ENGINE = InnoDB;
28 
29-- 3. Performance Reviews
30CREATE TABLE performance_reviews (
31    review_id INT AUTO_INCREMENT PRIMARY KEY,
32    emp_id INT NOT NULL,
33    review_period VARCHAR(10) NOT NULL, -- e.g. '2026-Q1'
34    rating DECIMAL(2, 1) NOT NULL CHECK (rating BETWEEN 1.0 AND 5.0),
35    CONSTRAINT fk_review_emp 
36        FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
37) ENGINE = InnoDB;

2. Inserting Realistic Enterprise Seed Data

1-- Seed Departments
2INSERT INTO departments (dept_id, dept_name, location, annual_budget) VALUES
3(1, 'Executive', 'New York', 2000000.00),
4(2, 'Engineering', 'San Francisco', 5000000.00),
5(3, 'Sales & Marketing', 'New York', 3000000.00);
6 
7-- Seed Org Hierarchy
8INSERT INTO employees (emp_id, dept_id, manager_id, first_name, last_name, job_title, salary, hire_date) VALUES
9(1, 1, NULL, 'Sarah', 'Connor', 'CEO', 450000.00, '2018-01-15'),
10(2, 2, 1, 'Alex', 'Murphy', 'VP of Engineering', 280000.00, '2019-03-01'),
11(3, 3, 1, 'Jordan', 'Belfort', 'VP of Sales', 260000.00, '2019-06-15'),
12(4, 2, 2, 'Ada', 'Lovelace', 'Principal Architect', 210000.00, '2020-01-10'),
13(5, 2, 2, 'Alan', 'Turing', 'Lead Dev', 180000.00, '2020-05-20'),
14(6, 2, 5, 'Grace', 'Hopper', 'Senior Dev', 150000.00, '2021-02-14'),
15(7, 2, 5, 'Linus', 'Torvalds', 'Software Engineer', 125000.00, '2022-08-01'),
16(8, 3, 3, 'Don', 'Draper', 'Account Director', 160000.00, '2021-09-01');
17 
18-- Seed Performance Reviews
19INSERT INTO performance_reviews (emp_id, review_period, rating) VALUES
20(4, '2026-Q1', 4.9),
21(5, '2026-Q1', 4.7),
22(6, '2026-Q1', 4.8),
23(7, '2026-Q1', 4.2),
24(8, '2026-Q1', 4.5);

3. Executive Analytics Queries

Query 1: Complete Organizational Hierarchy Traversal (Recursive CTE)

Unroll the reporting chain from the CEO down to junior developers:

1WITH RECURSIVE OrgTree AS (
  -- 1. Anchor: CEO (Level 1)
  SELECT 
      emp_id,
      CONCAT(first_name, ' ', last_name) AS employee_name,
      job_title,
      manager_id,
      1 AS org_level,
      CAST(first_name AS CHAR(255)) AS management_chain
  FROM employees
  WHERE manager_id IS NULL
12 
  UNION ALL
14 
  -- 2. Recursive Member: Direct Reports
  SELECT 
      e.emp_id,
      CONCAT(e.first_name, ' ', e.last_name),
      e.job_title,
      e.manager_id,
      o.org_level + 1,
      CONCAT(o.management_chain, ' -> ', e.first_name)
  FROM employees e
  JOIN OrgTree o ON e.manager_id = o.emp_id
25)
26SELECT 
  org_level,
  employee_name,
  job_title,
  management_chain
31FROM OrgTree
32ORDER BY management_chain;

Query 2: Department Salary Benchmarking with Window Functions

Compare each employee's compensation against departmental averages and quartiles:

1SELECT 
  d.dept_name,
  CONCAT(e.first_name, ' ', e.last_name) AS staff_name,
  e.job_title,
  e.salary,
  -- Departmental salary metrics
  ROUND(AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS dept_avg_salary,
  ROUND(e.salary - AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS diff_from_dept_avg,
  -- Departmental rank
  DENSE_RANK() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dept_salary_rank,
  -- Quartile bucket within department
  NTILE(2) OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_tier
13FROM employees e
14JOIN departments d ON e.dept_id = d.dept_id
15ORDER BY d.dept_name, dept_salary_rank;

Query 3: Department Budget Utilization & Headcount View

1CREATE OR REPLACE VIEW v_department_payroll_summary AS
2SELECT 
3    d.dept_id,
4    d.dept_name,
5    d.annual_budget,
6    COUNT(e.emp_id) AS headcount,
7    COALESCE(SUM(e.salary), 0.00) AS total_payroll,
8    ROUND(d.annual_budget - COALESCE(SUM(e.salary), 0.00), 2) AS remaining_budget,
9    ROUND((COALESCE(SUM(e.salary), 0.00) / d.annual_budget) * 100, 1) AS budget_consumed_pct
10FROM departments d
11LEFT JOIN employees e ON d.dept_id = e.dept_id
12GROUP BY d.dept_id, d.dept_name, d.annual_budget;
13 
14-- Query payroll summary:
15SELECT * FROM v_department_payroll_summary;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In our recursive OrgTree query, what constitutes the Anchor member?

Question 2

What does CAST(first_name AS CHAR(255)) accomplish in the Anchor member of the recursive CTE?

Question 3

Which window function was used to calculate the departmental salary ranking without skipping rank numbers for ties?

Question 4

What does the expression salary - AVG(salary) OVER(PARTITION BY dept_id) calculate?

Question 5

Why is ON DELETE SET NULL appropriate for the manager_id foreign key constraint?

Related Lessons

Previous Lesson	Next Lesson
Project 2: Banking Transaction & Ledger System with ACID Guarantees	None

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Intermediate20 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Capstone Project 3: Corporate Employee Hierarchy & Departmental Analytics Engine

Enterprise organizations are complex structures containing multi-tier managerial hierarchies, cross-departmental budgets, and salary grades.

1. Database Schema & Architecture

Our schema models corporate organizational charts:

departments: Department names, locations, and annual budget caps.
employees: Staff records containing salaries, department associations, and a self-referencing manager_id.
performance_reviews: Quarterly performance scores (1.0 to 5.0) evaluated over time.

1CREATE DATABASE IF NOT EXISTS corporate_analytics_db;
2USE corporate_analytics_db;
3 
4-- 1. Departments
5CREATE TABLE departments (
6    dept_id INT AUTO_INCREMENT PRIMARY KEY,
7    dept_name VARCHAR(50) NOT NULL UNIQUE,
8    location VARCHAR(50) NOT NULL,
9    annual_budget DECIMAL(15, 2) NOT NULL
10) ENGINE = InnoDB;
11 
12-- 2. Employees with Hierarchical Self-Reference
13CREATE TABLE employees (
14    emp_id INT AUTO_INCREMENT PRIMARY KEY,
15    dept_id INT NOT NULL,
16    manager_id INT NULL,
17    first_name VARCHAR(50) NOT NULL,
18    last_name VARCHAR(50) NOT NULL,
19    job_title VARCHAR(50) NOT NULL,
20    salary DECIMAL(12, 2) NOT NULL,
21    hire_date DATE NOT NULL,
22    CONSTRAINT fk_emp_dept 
23        FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
24    CONSTRAINT fk_emp_manager 
25        FOREIGN KEY (manager_id) REFERENCES employees(emp_id) 
26        ON DELETE SET NULL
27) ENGINE = InnoDB;
28 
29-- 3. Performance Reviews
30CREATE TABLE performance_reviews (
31    review_id INT AUTO_INCREMENT PRIMARY KEY,
32    emp_id INT NOT NULL,
33    review_period VARCHAR(10) NOT NULL, -- e.g. '2026-Q1'
34    rating DECIMAL(2, 1) NOT NULL CHECK (rating BETWEEN 1.0 AND 5.0),
35    CONSTRAINT fk_review_emp 
36        FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
37) ENGINE = InnoDB;

2. Inserting Realistic Enterprise Seed Data

1-- Seed Departments
2INSERT INTO departments (dept_id, dept_name, location, annual_budget) VALUES
3(1, 'Executive', 'New York', 2000000.00),
4(2, 'Engineering', 'San Francisco', 5000000.00),
5(3, 'Sales & Marketing', 'New York', 3000000.00);
6 
7-- Seed Org Hierarchy
8INSERT INTO employees (emp_id, dept_id, manager_id, first_name, last_name, job_title, salary, hire_date) VALUES
9(1, 1, NULL, 'Sarah', 'Connor', 'CEO', 450000.00, '2018-01-15'),
10(2, 2, 1, 'Alex', 'Murphy', 'VP of Engineering', 280000.00, '2019-03-01'),
11(3, 3, 1, 'Jordan', 'Belfort', 'VP of Sales', 260000.00, '2019-06-15'),
12(4, 2, 2, 'Ada', 'Lovelace', 'Principal Architect', 210000.00, '2020-01-10'),
13(5, 2, 2, 'Alan', 'Turing', 'Lead Dev', 180000.00, '2020-05-20'),
14(6, 2, 5, 'Grace', 'Hopper', 'Senior Dev', 150000.00, '2021-02-14'),
15(7, 2, 5, 'Linus', 'Torvalds', 'Software Engineer', 125000.00, '2022-08-01'),
16(8, 3, 3, 'Don', 'Draper', 'Account Director', 160000.00, '2021-09-01');
17 
18-- Seed Performance Reviews
19INSERT INTO performance_reviews (emp_id, review_period, rating) VALUES
20(4, '2026-Q1', 4.9),
21(5, '2026-Q1', 4.7),
22(6, '2026-Q1', 4.8),
23(7, '2026-Q1', 4.2),
24(8, '2026-Q1', 4.5);

3. Executive Analytics Queries

Query 1: Complete Organizational Hierarchy Traversal (Recursive CTE)

Unroll the reporting chain from the CEO down to junior developers:

1WITH RECURSIVE OrgTree AS (
  -- 1. Anchor: CEO (Level 1)
  SELECT 
      emp_id,
      CONCAT(first_name, ' ', last_name) AS employee_name,
      job_title,
      manager_id,
      1 AS org_level,
      CAST(first_name AS CHAR(255)) AS management_chain
  FROM employees
  WHERE manager_id IS NULL
12 
  UNION ALL
14 
  -- 2. Recursive Member: Direct Reports
  SELECT 
      e.emp_id,
      CONCAT(e.first_name, ' ', e.last_name),
      e.job_title,
      e.manager_id,
      o.org_level + 1,
      CONCAT(o.management_chain, ' -> ', e.first_name)
  FROM employees e
  JOIN OrgTree o ON e.manager_id = o.emp_id
25)
26SELECT 
  org_level,
  employee_name,
  job_title,
  management_chain
31FROM OrgTree
32ORDER BY management_chain;

Query 2: Department Salary Benchmarking with Window Functions

Compare each employee's compensation against departmental averages and quartiles:

1SELECT 
  d.dept_name,
  CONCAT(e.first_name, ' ', e.last_name) AS staff_name,
  e.job_title,
  e.salary,
  -- Departmental salary metrics
  ROUND(AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS dept_avg_salary,
  ROUND(e.salary - AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS diff_from_dept_avg,
  -- Departmental rank
  DENSE_RANK() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dept_salary_rank,
  -- Quartile bucket within department
  NTILE(2) OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_tier
13FROM employees e
14JOIN departments d ON e.dept_id = d.dept_id
15ORDER BY d.dept_name, dept_salary_rank;

Query 3: Department Budget Utilization & Headcount View

1CREATE OR REPLACE VIEW v_department_payroll_summary AS
2SELECT 
3    d.dept_id,
4    d.dept_name,
5    d.annual_budget,
6    COUNT(e.emp_id) AS headcount,
7    COALESCE(SUM(e.salary), 0.00) AS total_payroll,
8    ROUND(d.annual_budget - COALESCE(SUM(e.salary), 0.00), 2) AS remaining_budget,
9    ROUND((COALESCE(SUM(e.salary), 0.00) / d.annual_budget) * 100, 1) AS budget_consumed_pct
10FROM departments d
11LEFT JOIN employees e ON d.dept_id = e.dept_id
12GROUP BY d.dept_id, d.dept_name, d.annual_budget;
13 
14-- Query payroll summary:
15SELECT * FROM v_department_payroll_summary;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In our recursive OrgTree query, what constitutes the Anchor member?

Question 2

What does CAST(first_name AS CHAR(255)) accomplish in the Anchor member of the recursive CTE?

Question 3

Which window function was used to calculate the departmental salary ranking without skipping rank numbers for ties?

Question 4

What does the expression salary - AVG(salary) OVER(PARTITION BY dept_id) calculate?

Question 5

Why is ON DELETE SET NULL appropriate for the manager_id foreign key constraint?

Related Lessons

Previous Lesson	Next Lesson
Project 2: Banking Transaction & Ledger System with ACID Guarantees	None

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.