Blog

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

SourceLinkedIn

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:

employee_idsalarysalary_diff
260000.000.00
355000.005000.00
465000.005000.00
150000.0010000.00
570000.0010000.00

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

employee_idsalarysalary_diff
260000.000.00

View on DB Fiddle


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

Spread the love

Recent Posts

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science Welcome back…

6 days ago

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing Understanding…

6 days ago

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science Welcome back…

6 days ago

Day 4 of Learning Python for Data Science

Day 4 of Learning Python for Data Science Day 4 of Learning Python for Data…

6 days ago

Practice Questions and Answers for Day 3 of Learning Python for Data Science

Test your Python skills with these 20 practice questions and solutions from Day 3 of…

7 days ago

Day 3 of Learning Python for Data Science

Understanding Python’s conditional statements is essential for controlling the flow of a program. Today, we…

7 days ago