UptoSkills Logo
Login
Find Departments with Above Average Salary SpendingMedium

Find Departments with Above Average Salary Spending

MediumSQLPractice library · +20 XPsubqueriessqlaggregation

A company wants to analyze its salary distribution. They need to identify departments that are spending more on salaries than the company's average department spending.

Tables:

  • Employees (employee_id INT PRIMARY KEY, department_id INT, salary NUMERIC(10, 2))
  • Departments (department_id INT PRIMARY KEY, department_name VARCHAR(50))

Return the department_name for departments whose total salary cost exceeds the average total salary cost across all departments. If no departments meet this criterion, return an empty result set. Handle NULL salaries by treating them as 0 for calculations. If there's a tie in average spending, include all tied departments. Order the results alphabetically by department_name.

Tables

CREATE TABLE Departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);
CREATE TABLE Employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER REFERENCES Departments(department_id),
    salary NUMERIC(10, 2)
);

Example data

INSERT INTO Departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'Marketing'),
(4, 'HR');

INSERT INTO Employees (employee_id, department_id, salary)
VALUES
(101, 1, 90000.00),
(102, 1, 85000.00),
(103, 2, 70000.00),
(104, 2, 75000.00),
(105, 3, 60000.00),
(106, 3, 65000.00),
(107, 4, 50000.00),
(108, 1, 95000.00);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems