Write a query to identify the employee(s) whose salary is closest to the average salary of the company.

This question was asked in Interview at black rock. The open position had a CTC of 26 LPA. Read more

Company: BlackRock

CTC: 26LPA

Source: LinkedIn

SQL Interview Question

Q. Write a query to identify the employee(s) whose salary is closest to the average salary of the company

-- Create the Employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert some sample data
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES
(1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Michael', 'Johnson', 55000),
(4, 'Emily', 'Davis', 65000),
(5, 'Chris', 'Brown', 70000);

see this code on db-fiddle

Solution

WITH avg_sal AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT 
    employee_id, 
    salary, 
    ABS(salary - (SELECT avg_salary FROM avg_sal)) AS salary_diff
FROM employees
ORDER BY salary_diff ASC
LIMIT 1;

Explanation

In this scenario, our goal was to determine how close or far each employee’s salary is from the average salary, whether on the positive or negative side. To achieve this, we needed to calculate the average salary and then find the absolute (non-negative) difference between each employee’s salary and the average salary.

We calculated the average salary using a query inside a CTE:

SELECT AVG(salary) AS avg_salary FROM employees

Using the CTE ensures that SQL computes the average salary only once and avoids recalculating it for each row, improving efficiency.

In the main query, we selected employee_id, salary, and the absolute difference between each employee’s salary and the average salary. The ABS function was used to ensure the difference is non-negative. Finally, we ordered the results in ascending order of the salary difference and used the LIMIT clause to select the salary closest to the average.

Before applying the LIMIT clause, the result looks like this:

The limit clause allows us to pick the row with the smallest salary_diff, helping us identify the salary closest to the average.

View on DB Fiddle

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

• Day 13 of Learning Python for Data Science: Mastering Pivot, Apply and RegEx
• Practice day 12 of Learning Python for Data Science
• Day 12 of Learning Python for Data Science – Pandas
• Day 10 Of Learning Python for Data Science – NumPy Array In Python
• Day 9 of Learning Python for Data Science – Queries Related To Functions In Python