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