This case study is contained within the Serious SQL by Danny Ma.
- 🛠️ Requirements
- 📂 Data Overview
- 🚀 Solution
- 🧙♂️ Result
HR Analytica team requires 2 separate analytical views to be created using a single SQL script for two separate data assets that can be used for reporting purposes.
A current snapshot of the information is required to power HR Analytica’s People Analytics dashboard and Employee Deep Dive shown above.
The following data requirements is as follows:
Click to view
🌟 Company Level Insights
- Total number of employees
- Average company tenure in years
- Gender ratios
- Average payrise percentage and amount
🌟 Department Level Insights
- Number of employees in each department
- Current department manager tenure in years
- Gender ratios
- Average payrise percentage and amount
Title Level Insights
- Number of employees with each title
- Minimum, average, standard deviation of salaries
- Average total company tenure
- Gender ratios
- Average payrise percentage and amount
A historic data asset is also required by HR Analytica so their People Analytics team can perform deep dives into a specific employee’s history. This analysis is used for decision making when it comes to pay rises and promotions.
Click to view
🌟 Individual Employee Deep Dive
- 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
Hooray! Finally, this is what out final input looks like:
Source: Serious SQL
Source: Serious SQL
Contributions, issues, and feature requests are welcome!
To contribute to Patronify, see the GitHub documentation on creating a pull request.
Give a ⭐️ if you like this project!
© 2021 Leah Nguyen