Stephen's Data Deck - SQL: NigerianHR

SQL: NigerianHR_Analysis

Project Overview

Background

Compensation fairness and performance alignment are two of the most critical levers in workforce management. When pay and performance are misaligned, either deliberately or through structural neglect, the consequences compound over time: high performers disengage, underperformers are inadvertently retained, and the organisation’s ability to attract and develop talent erodes. This project used SQL to audit the compensation and performance landscape of a Nigerian organisation, identifying where those misalignments exist and how severe they are.

Objective

The analysis addressed seven questions spanning departmental pay comparison, internal salary spread, outlier detection, payroll concentration, pay-performance correlation, overcompensated underperformers, and underpaid high performers, building progressively toward a full diagnostic of the organisation’s compensation health.

Data Source

The dataset comprised 500 employee records across 7 departments: IT, Finance, Sales, Marketing, Operations, Human Resources, and Administration. Each record included Employee ID, department, salary, hire date, performance score (1.0–5.0), and location across Nigerian cities.

Methodology

All analysis was conducted in MySQL using a range of techniques including GROUP BY aggregation, MIN/MAX spread calculation, subquery-based outlier filtering, window functions (NTILE, RANK), payroll concentration ratios, multi-condition WHERE clauses, and cross-joining department averages for individual-level deviation scoring. Each query was designed to answer a specific business question while building toward a unified diagnostic picture of compensation equity and performance alignment.

Q1 — Departmental Average Salaries

IT leads compensation at ₦783,724 average salary, nearly three times the Administration average of ₦260,488, the lowest in the organisation. The ₦523,236 gap between the highest and lowest-paid departments raises immediate questions about internal pay equity and whether compensation philosophy is applied consistently across functions. This inter-departmental divide sets the context for every finding that follows.

Key insight: The compensation gap between IT and Administration is so wide that even IT’s lowest-paid employees earn more than the average Administration employee.

Q2 — Salary Spread Within Departments

Internal pay variation is most extreme in IT, with a ₦892,048 spread between its lowest (₦304,000) and highest (₦1,196,048) earners, wider than the entire salary range of some departments. Finance (₦531,131) and Sales (₦496,116) follow with significant internal variation, likely reflecting role tiering or tenure differences. Administration and Human Resources show the tightest spreads (₦248,731 and ₦262,191), indicating flat, undifferentiated pay structures with limited progression.

Key insight: IT’s wide spread is not just statistical, it reflects two distinct salary tiers coexisting within one department, a finding that becomes more significant in Q3.

Q3 — Salary Outliers by Department

Employees deviating more than 50% from their department average were identified across four departments. IT produced the most outliers at 10, uniquely split between high earners (3 employees 50%+ above average) and severely underpaid employees (7 employees 50%+ below average). This confirms IT’s bimodal pay structure. Sales (5 outliers) and Administration (5 outliers) are all above-average earners, while Finance’s 2 outliers both earn 51–55% above their department mean. EMP0163 in Sales holds the largest positive deviation at 71% above average.

Key insight: IT’s outlier profile , high earners and low earners coexisting, signals an urgent need for formal pay banding and role-level salary structure within the department.

Q4 — Payroll Concentration (Top 20% of Earners)

Human Resources shows the highest payroll concentration at 14.09% , its top 20% of earners control a disproportionate share of the departmental payroll. Administration (13.28%) follows, meaning a small group of senior administrators earns significantly more than the majority, despite the department’s low overall average from Q1. IT has the lowest concentration at 10.86%, because its extremely high earners are distributed across a large headcount, diluting the percentage share. Across all departments, the range is narrow (10.86%–14.09%), suggesting no single department is dramatically more concentrated than others.

Key insight: Payroll concentration is relatively uniform across departments. The more material story lies in the absolute salary levels and outlier patterns identified in Q1–Q3.

Q5 — Do High Earners Perform Better?

A quartile analysis of all 500 employees reveals that pay quartile and performance score are largely independent. Among the highest-paid employees (predominantly IT), several of the organisation’s largest salaries belong to its lowest performers: EMP0146 earns ₦1,196,048 with a score of 1.65; EMP0321 earns ₦1,132,972 with 1.72. Conversely, in the lowest-paid quartile , dominated by Administration, employees like EMP0158 (₦154,014, score 4.97) and EMP0084 (₦201,005, score 4.90) are near-perfect performers earning poverty-level wages by organisational standards.

Key insight: The organisation has no functioning pay-for-performance mechanism. Salary and performance score show no meaningful correlation across the workforce.

Q6 — Overpaid Underperformers: Which Departments Are Most Exposed?

Employees earning above their department average with performance scores below 3.0 represent a direct compensation inefficiency. Administration has 22 such employees, the highest count, including multiple staff scoring below 2.0 while drawing above-average salaries. Operations follows with 18, where several employees score as low as 1.56 despite above-average pay. IT’s 16 flagged employees carry the greatest financial cost: Fatima Okafor earns ₦1,196,048 with a score of 1.65; Ngozi Ibrahim earns ₦1,132,972 with 1.72. Human Resources (14), Marketing (13), Finance (10), and Sales (12) complete the picture, every department has this problem, confirming it is not a localised issue but an organisation-wide failure.

Key insight: IT presents the highest absolute financial exposure. Administration and Operations present the highest headcount exposure. Both require immediate performance management intervention.

Q7 — Underpaid High Performers: Promotion Candidates

Employees scoring 4.5 or above while earning below their department average represent the organisation’s most urgent retention risk. Administration has the most candidates at 8, with strikingly low salaries: Blessing Okonkwo scores 4.97 on ₦154,014; Chinedu Bello scores 4.94 on ₦154,324. These are near-perfect performers earning the organisation’s lowest wages. Finance (7 candidates) and Human Resources (6) follow, both with employees scoring above 4.8 while earning below departmental averages. IT has just 3 candidates, as most IT high performers already earn above the departmental mean.

Key insight: 32 employees across 7 departments are high-performing and underpaid. Without targeted salary reviews, these are the employees most likely to leave, and the hardest to replace.

Closing Observations

Taken together, these seven analyses tell a coherent and concerning story. The organisation is paying for tenure or headcount rather than output. Its highest-paid department (IT) contains some of its worst performers. Its most committed employees are concentrated in its lowest-paid functions (Administration, Human Resources). And the simultaneous presence of overpaid underperformers and underpaid high achievers, in every single department, confirms that performance is not currently a meaningful input into compensation decisions.

Three interventions are most urgent. First, a formal pay banding exercise for IT to resolve its bimodal salary structure. Second, a performance-linked salary review targeting the 32 high-performing, underpaid employees identified in Q7, particularly in Administration. Third, a performance improvement programme for the employees flagged in Q6, especially those in IT where the cost of retaining underperformers is highest. The SQL queries developed in this project are not one-time outputs, they are reusable diagnostic tools that can be refreshed each performance cycle to monitor progress.

Codes

select *

from nigerian_hr_employee_dataset;

/* Section 1: Compensation Structure & Internal Equity

Q1: Which departments have the highest and lowest average salaries? */

select department, round(avg(salary)) AvgSalary

from nigerian_hr_employee_dataset

group by department

having round(avg(salary)) = (select max(a.avg_salary) max_salary

from (select department,round(avg(salary)) avg_salary

from nigerian_hr_employee_dataset

group by 1) a)

union all

select department, round(avg(salary)) AvgSalary

from nigerian_hr_employee_dataset

group by department

having round(avg(salary)) = (select min(a.avg_salary) min_salary

from (select department,round(avg(salary)) avg_salary

from nigerian_hr_employee_dataset

group by 1) a);

### Q2: How wide is the salary spread (max − min) inside each department?

select department, max(salary) max_salary, min(salary) min_salary, max(salary)-min(salary) salary_spread

from nigerian_hr_employee_dataset

group by department;

/* Q3: Identify employees whose salary significantly deviates from their department average:

* More than 50% above department average

* More than 50% below department average

Which departments show the highest number of such outliers? */

WITH dept_avg as (

select department, avg(salary) as avg_salary

from nigerian_hr_employee_dataset

group by department

)

select n.employee_id,n.department,n.salary,d.avg_salary,round((n.salary-d.avg_salary)/d.avg_salary*100) pct_difference

from nigerian_hr_employee_dataset n

join dept_avg d

on n.department=d.department

where n.salary > d.avg_salary*1.5

or n.salary<d.avg_salary*0.5

order by n.department;

## Which departments show the highest number of such outliers?

WITH dept_avg as (

select department, avg(salary) as avg_salary

from nigerian_hr_employee_dataset

group by department

)

select n.department,count(*) outlier_count

from nigerian_hr_employee_dataset n

join dept_avg d

on n.department = d.department

WHERE n.Salary > 1.5 * d.avg_salary

OR n.Salary < 0.5 * d.avg_salary

GROUP BY n.Department

ORDER BY outlier_count DESC;

## Q4: Within each department, what percentage of total payroll is controlled by the top 20% highest-paid employees?

with dept as (select department, sum(salary) as tot_salary

from nigerian_hr_employee_dataset

group by department

ranked_employee as (select employee_id,department,salary, ntile(5) over (partition by department order by salary) salary_tile

from nigerian_hr_employee_dataset

top20_employee as ( select department, sum(salary) as top20_salary

from ranked_employee

where salary_tile= 1

group by department

)

select t.department, round(t.top20_salary/d.tot_salary*100,2) as payroll_concentration

from top20_employee t

join dept d

on t.department=d.department

order by payroll_concentration;

/* Section 2: Performance & Productivity Alignment */

## Q5: Are high earners actually high performers?

select * from nigerian_hr_employee_dataset;

with payroll as (select employee_id, department,salary,performance_score, ntile(4) over (order by salary) as salary_quartile

from nigerian_hr_employee_dataset

)

SELECT

employee_id,

department,

salary,

performance_score,

CASE

WHEN salary_quartile = 1 THEN 'Q1 - Top 25% (Highest Paid)'

WHEN salary_quartile = 2 THEN 'Q2 - Upper Mid 25%'

WHEN salary_quartile = 3 THEN 'Q3 - Lower Mid 25%'

WHEN salary_quartile = 4 THEN 'Q4 - Bottom 25% (Lowest Paid)'

END AS quartile_label

FROM payroll

ORDER BY salary_quartile, salary DESC;

## Q6: Identify employees earning above their department’s average salary but with performance scores below 3.0.

with dept_avg as(select department,round(avg(salary),2) as average

from nigerian_hr_employee_dataset

group by department

)

select n.full_name,n.department,n.salary,n.performance_score

from nigerian_hr_employee_dataset n

join dept_avg d

on n.department=d.department

where n.salary>d.average and n.performance_score < 3.0;

## Which departments are most exposed?

with dept_avg as(select department,round(avg(salary),2) as average

from nigerian_hr_employee_dataset

group by department

overpaid as (select n.full_name,n.department,n.salary,n.performance_score

from nigerian_hr_employee_dataset n

join dept_avg d

on n.department=d.department

where n.salary>d.average and n.performance_score < 3.0

)

select department, count(*)

from overpaid

group by department;

## Q7: Find employees with performance scores ≥ 4.5 but earning below their department average. These are potential promotion candidates. How many exist per department?

select * from nigerian_hr_employee_dataset;

with dept_avg as (select department,avg(salary) as average_salary

from nigerian_hr_employee_dataset

group by department

)

select n.full_name,n.department,n.salary,n.performance_score

from nigerian_hr_employee_dataset as n

join dept_avg as d

on n.department=d.department

where n.performance_score >= 4.5 and n.salary < d.average_salary;

Page updated

Google Sites

Report abuse