top of page
People Analytics.jpg

SmartHR: Employee Analytics Framework - SkyLine Tech

Project Overview​

Skyline Tech is a fast-growing global technology consulting firm. Its success relies on smart, data-driven decisions around workforce planning, salaries, and departmental performance. With HR and finance data scattered across systems, the company needed a scalable analytics solution to transform employee data into actionable insights.

The Challenge

Skyline Tech’s rapid growth exposed the limits of its existing HR and finance systems. Built quickly for initial operations, the infrastructure now relies on slow, manual spreadsheets and rigid batch processes. It cannot handle complex queries, provide real-time insights, or scale with increasing employee data, leaving the business “flying blind” on critical workforce metrics.​

Solution Overview​

The primary objective is to write production-level PySpark code necessary to answer this backlog, transforming raw data into actionable intelligence.

4.1.png
Data Preparation

Import Pyspark libraries and reads employee and department data stored in Delta format. It loads both datasets as DataFrames (emp_df and dep_df) and prints their schemas to understand the structure of each table before performing transformations or joins.

4.2.png
Employee and Salary Aggregations

 

  • Joins the employee and department tables using the department_id column and counts the number of employees in each department.

  • Groups data by department_name and computes the average, highest, and lowest salaries using PySpark’s aggregation functions.

These are key HR and organizational insights that reveal team sizes, compensation distribution, and pay structure across departments — valuable for workforce planning and budgeting.

4.3.png
Employee Service Duration and Hire Dates

​

  • Years of Service: Calculates how long each employee has been with the company using their hire date and the current date.

  • First and Last Hire Date per Department: Identifies when each department hired its first and most recent employee.

​Employee tenure and hiring timelines are crucial for analyzing retention, attrition, and department growth trends - helping HR measure stability and recruitment velocity.

4.4.png
Salary as Percentage of Department Average

  • Aggregates the average salary per department.

  • Joins it back to the employee dataset.

  • Computes the percentage difference and displays rounded results alongside the employee and department names.

This metric is useful for pay equity analysis — helping HR detect outliers, ensure fairness, and maintain consistency in compensation across similar roles or departments.

4.5.png
Running Totals and Salary Categorization

  • Computes a running total of salaries within each department ordered by hire date using window functions.

  • Adds a new column to classify employees as High, Medium, or Low earners based on salary thresholds.

Running totals help track cumulative payroll growth over time, while salary categorization aids in talent segmentation and compensation analysis, essential for budgeting and workforce planning.

4.6.png
Salary and Department Insights

 

  • Finds which department has the highest maximum salary by aggregating and sorting results.

  • Uses a left join to identify departments that have no employees assigned.

  • Groups by the year of hire to display the number of employees hired each year.

  • Computes how long each employee has been with the company in months using the months_between function.

These insights support operational decision-making — highlighting compensation benchmarks, identifying inactive departments, understanding hiring patterns, and analyzing workforce experience levels.

4.7.png
Highest Paid Employee per Department

  • Uses a window partition by department to rank employees by salary in descending order, then filters for the top-ranked employee to identify the highest-paid individual in each department

Supports leadership mapping, highlights top talent, and helps maintain compensation transparency within departments.

4.8.png
​Salary Difference and User-Defined Function (UDF) for Salary Grade

 

  • Computes the difference between each employee’s salary and the department’s average salary using a window function.

  • Defines a UDF (User-Defined Function) that assigns a salary grade:

  • ‘A’ for salary > 120K,

  • ‘B’ for 80K–120K,

  • ‘C’ for salary < 80K.

The salary difference enables pay gap analysis, and the UDF adds interpretability - useful for categorizing compensation tiers in HR dashboards.

4.9.png
Department with Highest Average Tenure

 

  • Calculates each employee’s tenure in months, then groups by department to find which department has the highest average employee tenure.

​Helps identify departments with strong employee retention and engagement—critical for workforce stability and cultural analysis.

bottom of page