This is a people analytics case study from the Serious SQL course by Danny Ma. The HR analytica team have asked us to construct datasets to answer basic reporting questions and also feed their bespoke People Analytics dashboards.
- 🧐 About
- 🎯 Getting Started
- 📊 Data Exploration
- 🧲 Join Implementation
- ✨ Final Solution
- 🚀 Business Questions
- 🎨 Contributing
- 🌟 Support
People Analytics or HR Analytics is an increasingly popular focus area for data professionals. Many business and people decisions which were traditionally based off senior management gut feels and intuition are starting to become more data-driven.
In this SQL case study - We’ve have been asked specifically to generate database views that HR Analytica team can use for 2 key dashboards, reporting solutions and ad-hoc analytics requests.
The following insights must be generated for the 2 dashboards requested by HR Analytica:
Click to View
- Total number of employees
- Average company tenure in years
- Gender ratios
- Average payrise percentage and amount
Click to View
- Number of employees in each department
- Current department manager tenure in years
- Gender ratios
- Average payrise percentage and amount
Click to View
- Number of employees with each title
- Minimum, average, standard deviation of salaries
- Average total company tenure
- Gender ratios
- Average payrise percentage and amount
The People Analytics dashboard that we need to power data to is shown as below:
Source: Serious SQL
Click to view
- See all the various employment history ordered by effective date including salary, department, manager and title changes
- Calculate previous historic payrise percentages and value changes
- Calculate the previous position and department history in months with start and end dates
- Compare an employee’s current salary, total company tenure, department, position and gender to the average benchmarks for their current position
The Deep Dive data dashboard is shown as below:
Source: Serious SQL
We start by doing the data exploration. There are 6 tables in total viz. employee, title, salary, department, department_employee & department_manager. The ERD diagram of the same is as follows.
Source: Serious SQL
Additionally - we’ve been notified about the presence of date issues with our datasets where there were data-entry issues related to all DATE related fields. I have fixed that in this section.
Next, we start implementing the table joins which will then help us to start the problem solving. From the analysis section, we have come to conclusion to the following join table sequence.
| Join Journey Part | Start | End | Foreign Key |
|---|---|---|---|
| Part 1 | employee |
title |
employee_id |
| Part 2 | employee |
salary |
employee_id |
| Part 3 | employee |
department_employee |
employee_id |
| Part 4 | department |
department_employee |
department_id |
| Part 5 | department |
department_manager |
department_id |
After implementing the joins, we begin solving for the problems as required by the HR Analytica team. Our solution is divided into two parts viz. 1. Current Employee Snapshot where we have created data assets that can power the first dashboard and 2. Historic Employee Snapshot which will power the second dashboard and contain all the details of the employees at individual level.
Lastly, there are a few questions asked by the HR Analytica team and they can be divided into 3 different sections based on the analytical focus areas. They are as follows.
Click to view questions
- What is the full name of the employee with the highest salary?
- How many current employees have the equal longest time in their current positions?
- Which department has the least number of current employees?
- What is the largest difference between minimimum and maximum salary values for all current employees?
- How many male employees are above the average salary value for the Production department?
- Which title has the highest average salary for male employees?
- Which department has the highest average salary for female employees?
- Which department has the most female employees?
- What is the gender ratio in the department which has the highest average male salary and what is the average male salary value for that department?
- HR Analytica want to change the average salary increase percentage value to 2 decimal places - what will the new value be for males for the company level dashboard?
Click to view questions
- How many employees have left the company?
- What percentage of churn employees were male?
- Which title had the most churn?
- Which department had the most churn?
- Which year had the most churn?
- What was the average salary for each employee who has left the company?
- What was the median total company tenure for each churn employee just before they left?
- On average, how many different titles did each churn employee hold?
- What was the average last pay increase for churn employees?
- What proportion of churn employees had a pay decrease event in their last 5 events?
- How many current employees have the equal longest overall time in their current positions (not in years)?
Click to view questions
- How many managers are there currently in the company?
- How many employees have ever been a manager?
- On average - how long did it take for an employee to first become a manager from their the date they were originally hired?
- What was the most common titles that managers had just before before they became a manager?
- On average - how much more do current managers make on average compared to all other employees?
Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.
- Fork the Project
- Create your Feature Branch (
git checkout -b feature/AmazingFeature) - Commit your Changes (
git commit -m 'Add some AmazingFeature') - Push to the Branch (
git push origin feature/AmazingFeature) - Open a Pull Request
Please hit the ⭐button if you like this project. 😄