Bài 10: Project - HR Analytics Dashboard

HR Analytics Dashboard

🎯 Project Overview

Trong project này, bạn sẽ đóng vai HR Data Analyst tại Adventure Works Cycles. Nhiệm vụ là xây dựng các reports và analytics để hỗ trợ quyết định HR.

Business Context

Adventure Works cần insights về:

Workforce Composition: Cấu trúc nhân sự theo department, job title
Compensation Analysis: Phân tích lương, so sánh với market
Employee Tenure: Phân tích thâm niên và retention
Organizational Health: Metrics về structure và efficiency

📊 AdventureWorks HR Tables

Key Tables

Text

1HumanResources.Employee           - Employee basic info
2HumanResources.Department         - Department list
3HumanResources.EmployeeDepartmentHistory - Employee-Dept mapping
4HumanResources.EmployeePayHistory - Salary history
5HumanResources.Shift              - Work shifts
6HumanResources.JobCandidate       - Job applicants
7Person.Person                     - Personal details
8Person.PersonPhone                - Phone numbers
9Person.EmailAddress               - Email addresses
10Person.Address                    - Physical addresses

ERD Overview

Text

1Person.Person (BusinessEntityID)
2       │
3       ├──► HumanResources.Employee (BusinessEntityID)
4       │          │
5       │          ├──► EmployeePayHistory (BusinessEntityID)
6       │          │
7       │          └──► EmployeeDepartmentHistory ──► Department
8       │                     │
9       │                     └──► Shift
10       │
11       ├──► Person.PersonPhone
12       ├──► Person.EmailAddress
13       └──► Person.BusinessEntityAddress ──► Address

📝 Project Tasks

Task 1: Employee Directory Report

Objective

Tạo comprehensive employee directory với đầy đủ thông tin.

Requirements:

Full name, Job Title, Department
Email, Phone
Hire Date, Years with company
Current hourly rate
Marital status, Gender

SQL

1-- YOUR CODE HERE
2-- Hint: JOIN Employee, Person, Department, PayHistory, EmailAddress, Phone

💡 Xem giải pháp

SQL

1SELECT 
2    e.BusinessEntityID AS EmployeeID,
3    p.FirstName + ' ' + ISNULL(p.MiddleName + ' ', '') + p.LastName AS FullName,
4    e.JobTitle,
5    d.Name AS Department,
6    d.GroupName AS DepartmentGroup,
7    ea.EmailAddress,
8    pp.PhoneNumber,
9    e.HireDate,
10    DATEDIFF(YEAR, e.HireDate, GETDATE()) AS YearsWithCompany,
11    eph.Rate AS CurrentHourlyRate,
12    CASE e.MaritalStatus WHEN 'M' THEN 'Married' WHEN 'S' THEN 'Single' END AS MaritalStatus,
13    CASE e.Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,
14    e.BirthDate,
15    DATEDIFF(YEAR, e.BirthDate, GETDATE()) AS Age
16FROM HumanResources.Employee e
17JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
18JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
19    AND edh.EndDate IS NULL  -- Current department only
20JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
21JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
22LEFT JOIN Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID
23LEFT JOIN Person.PersonPhone pp ON p.BusinessEntityID = pp.BusinessEntityID
24WHERE eph.RateChangeDate = (
25    SELECT MAX(RateChangeDate) 
26    FROM HumanResources.EmployeePayHistory 
27    WHERE BusinessEntityID = e.BusinessEntityID
28)
29ORDER BY d.Name, p.LastName, p.FirstName;

Task 2: Department Headcount Analysis

Objective

Phân tích headcount theo department với breakdown chi tiết.

Requirements:

Employee count per department
% of total workforce
Average salary per department
Department group totals (using ROLLUP)

SQL

1-- YOUR CODE HERE

💡 Xem giải pháp

SQL

1WITH DepartmentStats AS (
2    SELECT 
3        d.GroupName,
4        d.Name AS Department,
5        COUNT(*) AS Headcount,
6        AVG(eph.Rate) AS AvgHourlyRate,
7        MIN(eph.Rate) AS MinRate,
8        MAX(eph.Rate) AS MaxRate
9    FROM HumanResources.Employee e
10    JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
11    JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
12    JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
13    WHERE edh.EndDate IS NULL
14    AND eph.RateChangeDate = (
15        SELECT MAX(RateChangeDate) 
16        FROM HumanResources.EmployeePayHistory 
17        WHERE BusinessEntityID = e.BusinessEntityID
18    )
19    GROUP BY d.GroupName, d.Name
20)
21SELECT 
22    COALESCE(GroupName, 'COMPANY TOTAL') AS GroupName,
23    COALESCE(Department, 'Group Subtotal') AS Department,
24    SUM(Headcount) AS Headcount,
25    CAST(SUM(Headcount) * 100.0 / SUM(SUM(Headcount)) OVER () AS DECIMAL(5,2)) AS PctOfTotal,
26    CAST(AVG(AvgHourlyRate) AS DECIMAL(10,2)) AS AvgHourlyRate,
27    CASE 
28        WHEN GROUPING(Department) = 1 AND GROUPING(GroupName) = 1 THEN 'Grand Total'
29        WHEN GROUPING(Department) = 1 THEN 'Group Total'
30        ELSE 'Department'
31    END AS RowType
32FROM DepartmentStats
33GROUP BY ROLLUP (GroupName, Department)
34ORDER BY 
35    GROUPING(GroupName), GroupName,
36    GROUPING(Department), Department;

Task 3: Compensation Analysis

Objective

Phân tích chi tiết về compensation structure.

Requirements:

Salary distribution by department
Pay band analysis (Low/Mid/High)
Gender pay gap analysis
Top earners per department

SQL

1-- Part A: Salary distribution
2-- YOUR CODE HERE

💡 Xem giải pháp Part A

SQL

1-- Salary distribution with percentiles
2SELECT DISTINCT
3    d.Name AS Department,
4    COUNT(*) OVER (PARTITION BY d.DepartmentID) AS EmployeeCount,
5    AVG(eph.Rate) OVER (PARTITION BY d.DepartmentID) AS AvgRate,
6    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY eph.Rate) 
7        OVER (PARTITION BY d.DepartmentID) AS Rate_25thPct,
8    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY eph.Rate) 
9        OVER (PARTITION BY d.DepartmentID) AS Rate_Median,
10    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY eph.Rate) 
11        OVER (PARTITION BY d.DepartmentID) AS Rate_75thPct,
12    MIN(eph.Rate) OVER (PARTITION BY d.DepartmentID) AS MinRate,
13    MAX(eph.Rate) OVER (PARTITION BY d.DepartmentID) AS MaxRate
14FROM HumanResources.Employee e
15JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
16JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
17JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
18WHERE edh.EndDate IS NULL
19AND eph.RateChangeDate = (
20    SELECT MAX(RateChangeDate) 
21    FROM HumanResources.EmployeePayHistory 
22    WHERE BusinessEntityID = e.BusinessEntityID
23)
24ORDER BY Department;

SQL

1-- Part B: Pay band analysis
2-- YOUR CODE HERE

💡 Xem giải pháp Part B

SQL

1-- Pay band analysis
2WITH EmployeeRates AS (
3    SELECT 
4        e.BusinessEntityID,
5        e.Gender,
6        d.Name AS Department,
7        eph.Rate,
8        NTILE(3) OVER (ORDER BY eph.Rate) AS PayBand
9    FROM HumanResources.Employee e
10    JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
11    JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
12    JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
13    WHERE edh.EndDate IS NULL
14    AND eph.RateChangeDate = (
15        SELECT MAX(RateChangeDate) 
16        FROM HumanResources.EmployeePayHistory 
17        WHERE BusinessEntityID = e.BusinessEntityID
18    )
19)
20SELECT 
21    CASE PayBand 
22        WHEN 1 THEN 'Entry Level'
23        WHEN 2 THEN 'Mid Level'
24        WHEN 3 THEN 'Senior Level'
25    END AS PayBand,
26    COUNT(*) AS EmployeeCount,
27    AVG(Rate) AS AvgRate,
28    MIN(Rate) AS MinRate,
29    MAX(Rate) AS MaxRate,
30    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS MaleCount,
31    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS FemaleCount
32FROM EmployeeRates
33GROUP BY PayBand
34ORDER BY PayBand;

SQL

1-- Part C: Gender pay analysis
2-- YOUR CODE HERE

💡 Xem giải pháp Part C

SQL

1-- Gender pay gap by department
2SELECT 
3    d.Name AS Department,
4    AVG(CASE WHEN e.Gender = 'M' THEN eph.Rate END) AS AvgMaleRate,
5    AVG(CASE WHEN e.Gender = 'F' THEN eph.Rate END) AS AvgFemaleRate,
6    AVG(CASE WHEN e.Gender = 'M' THEN eph.Rate END) - 
7    AVG(CASE WHEN e.Gender = 'F' THEN eph.Rate END) AS PayGap,
8    CAST(
9        (AVG(CASE WHEN e.Gender = 'M' THEN eph.Rate END) - 
10         AVG(CASE WHEN e.Gender = 'F' THEN eph.Rate END)) * 100.0 /
11        NULLIF(AVG(CASE WHEN e.Gender = 'M' THEN eph.Rate END), 0)
12    AS DECIMAL(5,2)) AS PayGapPct
13FROM HumanResources.Employee e
14JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
15JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
16JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
17WHERE edh.EndDate IS NULL
18AND eph.RateChangeDate = (
19    SELECT MAX(RateChangeDate) 
20    FROM HumanResources.EmployeePayHistory 
21    WHERE BusinessEntityID = e.BusinessEntityID
22)
23GROUP BY d.DepartmentID, d.Name
24HAVING COUNT(CASE WHEN e.Gender = 'M' THEN 1 END) > 0
25   AND COUNT(CASE WHEN e.Gender = 'F' THEN 1 END) > 0
26ORDER BY ABS(PayGap) DESC;

Task 4: Tenure Analysis

Objective

Phân tích employee tenure và retention patterns.

Requirements:

Tenure distribution by department
Average tenure by job level
Tenure vs salary correlation
Long-tenured employees (5+ years)

SQL

1-- YOUR CODE HERE

💡 Xem giải pháp

SQL

1-- Comprehensive tenure analysis
2WITH TenureData AS (
3    SELECT 
4        e.BusinessEntityID,
5        p.FirstName + ' ' + p.LastName AS EmployeeName,
6        e.JobTitle,
7        d.Name AS Department,
8        d.GroupName,
9        e.HireDate,
10        DATEDIFF(YEAR, e.HireDate, GETDATE()) AS TenureYears,
11        DATEDIFF(MONTH, e.HireDate, GETDATE()) AS TenureMonths,
12        eph.Rate AS CurrentRate,
13        CASE 
14            WHEN DATEDIFF(YEAR, e.HireDate, GETDATE()) < 1 THEN 'New (<1 year)'
15            WHEN DATEDIFF(YEAR, e.HireDate, GETDATE()) < 3 THEN 'Junior (1-3 years)'
16            WHEN DATEDIFF(YEAR, e.HireDate, GETDATE()) < 5 THEN 'Mid (3-5 years)'
17            WHEN DATEDIFF(YEAR, e.HireDate, GETDATE()) < 10 THEN 'Senior (5-10 years)'
18            ELSE 'Veteran (10+ years)'
19        END AS TenureBand
20    FROM HumanResources.Employee e
21    JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
22    JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
23    JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
24    JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
25    WHERE edh.EndDate IS NULL
26    AND eph.RateChangeDate = (
27        SELECT MAX(RateChangeDate) 
28        FROM HumanResources.EmployeePayHistory 
29        WHERE BusinessEntityID = e.BusinessEntityID
30    )
31)
32
33-- Summary by tenure band
34SELECT 
35    TenureBand,
36    COUNT(*) AS EmployeeCount,
37    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(5,2)) AS PctOfTotal,
38    CAST(AVG(CurrentRate) AS DECIMAL(10,2)) AS AvgHourlyRate,
39    AVG(TenureYears) AS AvgTenureYears
40FROM TenureData
41GROUP BY TenureBand
42ORDER BY 
43    CASE TenureBand
44        WHEN 'New (<1 year)' THEN 1
45        WHEN 'Junior (1-3 years)' THEN 2
46        WHEN 'Mid (3-5 years)' THEN 3
47        WHEN 'Senior (5-10 years)' THEN 4
48        WHEN 'Veteran (10+ years)' THEN 5
49    END;

Task 5: Department Transfer Analysis

Objective

Phân tích internal mobility - employees đã chuyển department.

Requirements:

Employees với multiple department history
Most common transfer paths
Average time between transfers

SQL

1-- YOUR CODE HERE

💡 Xem giải pháp

SQL

1-- Employees with department transfers
2WITH DeptHistory AS (
3    SELECT 
4        edh.BusinessEntityID,
5        p.FirstName + ' ' + p.LastName AS EmployeeName,
6        d.Name AS Department,
7        edh.StartDate,
8        edh.EndDate,
9        ROW_NUMBER() OVER (PARTITION BY edh.BusinessEntityID ORDER BY edh.StartDate) AS DeptOrder
10    FROM HumanResources.EmployeeDepartmentHistory edh
11    JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
12    JOIN Person.Person p ON edh.BusinessEntityID = p.BusinessEntityID
13),
14Transfers AS (
15    SELECT 
16        d1.BusinessEntityID,
17        d1.EmployeeName,
18        d1.Department AS FromDept,
19        d2.Department AS ToDept,
20        d1.EndDate AS TransferDate,
21        DATEDIFF(DAY, d1.StartDate, d1.EndDate) AS DaysInPrevDept
22    FROM DeptHistory d1
23    JOIN DeptHistory d2 ON d1.BusinessEntityID = d2.BusinessEntityID 
24        AND d1.DeptOrder + 1 = d2.DeptOrder
25)
26
27-- Transfer summary
28SELECT 
29    FromDept + ' → ' + ToDept AS TransferPath,
30    COUNT(*) AS TransferCount,
31    AVG(DaysInPrevDept) AS AvgDaysBeforeTransfer
32FROM Transfers
33GROUP BY FromDept, ToDept
34ORDER BY TransferCount DESC;

Task 6: Organizational Hierarchy

Objective

Analyze reporting structure và organizational depth.

Requirements:

Org chart với reporting hierarchy
Span of control per manager
Organizational depth

SQL

1-- YOUR CODE HERE

💡 Xem giải pháp

SQL

1-- Recursive CTE for org hierarchy
2WITH OrgHierarchy AS (
3    -- Anchor: CEO (no manager)
4    SELECT 
5        e.BusinessEntityID,
6        p.FirstName + ' ' + p.LastName AS EmployeeName,
7        e.JobTitle,
8        CAST(NULL AS INT) AS ManagerID,
9        CAST('N/A' AS NVARCHAR(200)) AS ManagerName,
10        0 AS OrgLevel,
11        CAST(p.FirstName + ' ' + p.LastName AS NVARCHAR(MAX)) AS OrgPath
12    FROM HumanResources.Employee e
13    JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
14    WHERE e.OrganizationLevel = 0
15    
16    UNION ALL
17    
18    -- Recursive: employees with managers
19    SELECT 
20        e.BusinessEntityID,
21        p.FirstName + ' ' + p.LastName AS EmployeeName,
22        e.JobTitle,
23        oh.BusinessEntityID AS ManagerID,
24        oh.EmployeeName AS ManagerName,
25        oh.OrgLevel + 1 AS OrgLevel,
26        CAST(oh.OrgPath + ' > ' + p.FirstName + ' ' + p.LastName AS NVARCHAR(MAX)) AS OrgPath
27    FROM HumanResources.Employee e
28    JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
29    JOIN OrgHierarchy oh ON e.OrganizationNode.GetAncestor(1) = 
30        (SELECT OrganizationNode FROM HumanResources.Employee WHERE BusinessEntityID = oh.BusinessEntityID)
31)
32SELECT 
33    REPLICATE('    ', OrgLevel) + EmployeeName AS Employee,
34    JobTitle,
35    ManagerName,
36    OrgLevel
37FROM OrgHierarchy
38ORDER BY OrgPath;
39
40-- Span of control
41SELECT 
42    m.BusinessEntityID AS ManagerID,
43    pm.FirstName + ' ' + pm.LastName AS ManagerName,
44    m.JobTitle,
45    COUNT(e.BusinessEntityID) AS DirectReports
46FROM HumanResources.Employee m
47JOIN Person.Person pm ON m.BusinessEntityID = pm.BusinessEntityID
48LEFT JOIN HumanResources.Employee e ON e.OrganizationNode.GetAncestor(1) = m.OrganizationNode
49GROUP BY m.BusinessEntityID, pm.FirstName, pm.LastName, m.JobTitle
50HAVING COUNT(e.BusinessEntityID) > 0
51ORDER BY DirectReports DESC;

Task 7: HR Dashboard Summary

Objective

Tạo executive summary với key HR metrics.

Requirements:

Total headcount
Average tenure
Average compensation
Department distribution
Gender distribution
Age distribution

SQL

1-- YOUR CODE HERE

💡 Xem giải pháp

SQL

1-- Executive HR Dashboard
2SELECT 
3    -- Headcount metrics
4    COUNT(*) AS TotalHeadcount,
5    
6    -- Tenure metrics
7    AVG(DATEDIFF(YEAR, e.HireDate, GETDATE())) AS AvgTenureYears,
8    MIN(e.HireDate) AS EarliestHireDate,
9    MAX(e.HireDate) AS LatestHireDate,
10    
11    -- Compensation metrics
12    AVG(eph.Rate) AS AvgHourlyRate,
13    MIN(eph.Rate) AS MinHourlyRate,
14    MAX(eph.Rate) AS MaxHourlyRate,
15    AVG(eph.Rate * 2080) AS AvgAnnualizedSalary,  -- Assuming 40hr/week, 52 weeks
16    
17    -- Demographics
18    SUM(CASE WHEN e.Gender = 'M' THEN 1 ELSE 0 END) AS MaleCount,
19    SUM(CASE WHEN e.Gender = 'F' THEN 1 ELSE 0 END) AS FemaleCount,
20    CAST(SUM(CASE WHEN e.Gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS FemalePct,
21    
22    -- Age metrics
23    AVG(DATEDIFF(YEAR, e.BirthDate, GETDATE())) AS AvgAge,
24    MIN(DATEDIFF(YEAR, e.BirthDate, GETDATE())) AS MinAge,
25    MAX(DATEDIFF(YEAR, e.BirthDate, GETDATE())) AS MaxAge,
26    
27    -- Marital status
28    SUM(CASE WHEN e.MaritalStatus = 'M' THEN 1 ELSE 0 END) AS MarriedCount,
29    SUM(CASE WHEN e.MaritalStatus = 'S' THEN 1 ELSE 0 END) AS SingleCount
30
31FROM HumanResources.Employee e
32JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
33WHERE eph.RateChangeDate = (
34    SELECT MAX(RateChangeDate) 
35    FROM HumanResources.EmployeePayHistory 
36    WHERE BusinessEntityID = e.BusinessEntityID
37);
38
39-- Department breakdown
40SELECT 
41    d.GroupName,
42    d.Name AS Department,
43    COUNT(*) AS Headcount,
44    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS DECIMAL(5,2)) AS PctOfTotal,
45    AVG(eph.Rate) AS AvgHourlyRate
46FROM HumanResources.Employee e
47JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
48JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID
49JOIN HumanResources.EmployeePayHistory eph ON e.BusinessEntityID = eph.BusinessEntityID
50WHERE edh.EndDate IS NULL
51AND eph.RateChangeDate = (
52    SELECT MAX(RateChangeDate) 
53    FROM HumanResources.EmployeePayHistory 
54    WHERE BusinessEntityID = e.BusinessEntityID
55)
56GROUP BY d.GroupName, d.DepartmentID, d.Name
57ORDER BY d.GroupName, Headcount DESC;

🏆 Project Completion Checklist

Skills Demonstrated

Sau khi hoàn thành project này, bạn đã practiced:

📚 Course Completion

🎉 Congratulations! Bạn đã hoàn thành khóa học SQL Server cho Data Analyst!

Next Steps

Practice More: Thử tạo thêm reports với AdventureWorks
Real Projects: Apply skills vào real-world data
Advanced Topics:
- Stored Procedures
- Views và Indexed Views
- Performance Tuning
- SQL Server Reporting Services (SSRS)