Consolidated Assignment - HR Metrics and Insights

[akash-coded]

It will require the use of various SQL functionalities, including but not limited to, joins, subqueries, CTEs, stored procedures, functions, and performance optimization techniques.

HR Metrics and Insights

Context: As a Senior Data Engineer in the HR department of a multinational firm, you are tasked with providing insights into employee data. The company has been experiencing a high attrition rate, and the board is looking for data-driven insights to understand and combat this issue.

The HR database is your primary data source for this task.

Problem Statements:

1. Using subqueries, identify the departments with the highest attrition rate in the last year.
2. Design a stored procedure that, given an employee's ID, provides a comprehensive employment history of the employee, including their role changes, salary progression, and managers.
3. HR is planning to do a round of promotions. Use window functions to identify potential candidates for promotion based on their tenure and performance metrics.
4. The board requires a dynamic view of the employee data to monitor key HR metrics. Create a view that provides real-time insights into total headcount, average salary, attrition rate, and diversity metrics.
5. As an essential part of maintaining an efficient database, optimize the queries used in this assignment and justify the optimization techniques used.

This assignment is designed to emulate real-world scenarios and challenges that a data engineer would encounter in a business setting. The complexity lies in understanding the business context, applying the appropriate SQL concepts, and optimizing for performance. The assignments encourage not only technical proficiency but also strategic thinking and problem-solving skills.