Employee Database Analysis

Objective

Where does headcount concentrate, and which departments carry the highest average salary exposure?

A normalized employee database was queried across employee, department, salary, and title tables. Joins, filtering, grouping, and aggregate functions were used to profile the current workforce, then reconciled in Excel to confirm record counts and salary aggregates.

1. Step 1

   Scope the current population

   Salary and department assignment tables are effective-dated. Every query filters on the open-ended end date so headcount reflects current employees, not the full historical record.

2. Step 2

   Join across the entity graph

   Employees join to department assignment, then to department names, then to salary and title history using primary/foreign key relationships.

3. Step 3

   Aggregate and rank

   GROUP BY department with COUNT and AVG, ordered descending, to produce headcount and average current salary side by side.

4. Step 4

   Reconcile

   Results were exported to Excel and re-totaled independently to confirm the aggregate row counts matched the source tables.

SELECT
    d.dept_name                     AS department,
    COUNT(DISTINCT e.emp_no)        AS employees,
    ROUND(AVG(s.salary), 0)         AS avg_current_salary
FROM employees        AS e
JOIN dept_emp         AS de ON de.emp_no = e.emp_no
JOIN departments      AS d  ON d.dept_no = de.dept_no
JOIN salaries         AS s  ON s.emp_no  = e.emp_no
WHERE de.to_date = '9999-01-01'      -- current department assignment
  AND s.to_date  = '9999-01-01'      -- current salary record
GROUP BY d.dept_name
ORDER BY employees DESC;

• 240,124 employees are currently active once effective-dated rows are filtered — roughly 20K fewer than the raw employee table implies.
• Development is the largest department at 61,386 people, about 26% of the active workforce.
• Sales carries the highest average current salary at approximately $88,853, about 31% above Development.
• Headcount rank and salary rank are close to inverted: the three largest departments all sit in the bottom half on average pay.