Salary Gap Analysis: Hike Percentages to Reach Departmental Averages

From the provided employee data, identify employees whose salaries are below the department's average salary, and determine the percentage of salary increase required to reach the average departmental salary.

Input Data:

Expected Output:

Data Script:

CREATE TABLE EmployeeInfo
(
  EmployeeID INT,
  EmpName NVARCHAR(250) NULL,
  EmpSalary DECIMAL(36,4) NULL,
  EmpDepartment NVARCHAR(75) NULL
)
INSERT INTO EmployeeInfo VALUES
(100,'Jhon',45000,'HR'),
(101,'Alex',50000,'HR'),
(102,'Kim',5000,'HR'),
(103,'Sas',50000,'HR'),
(104,'Chari',100000,'Sales'),
(105,'Vasu',86000,'Sales'),
(106,'Sri',72000,'Sales'),
(107,'Mahesh',8915,'Sales'),
(108,'Asad',1200,'Marketing'),
(109,'Ghani',1250,'Marketing'),
(110,'KingS',10000,'Marketing'),
(111,'Pushpa',50000,'Marketing')

SELECT * FROM EmployeeInfo

Copy

Please try to answer the question, if you still not able to get it click below show button to see the answer

   SELECT 
   		EMP.*,s.AvgSalary, 
   		CASE WHEN EMP.EmpSalary < S.AvgSalary THEN (((S.AvgSalary-EMP.EmpSalary)/EMP.EmpSalary)*100) ELSE 0 END as 'Hike % Required'
   FROM EmployeeInfo EMP
   INNER JOIN
   (   SELECT EmpDepartment,AVG(EmpSalary) AvgSalary
       FROM EmployeeInfo 
       GROUP BY EmpDepartment
   ) AS S
   ON EMP.EmpDepartment=S.EmpDepartment
   WHERE EMP.EmpSalary < S.AvgSalary