Departments by Average Salary

Find departments whose average salary is above the company average.

Problem statement

Prompt

Given the department and employee tables below, write a query that returns the name of every department whose average salary is higher than the company-wide average salary (that is, the average over all employees).

Rules:

Return two columns: department (the department name) and avg_salary (that department's average salary, as an integer — CAST(ROUND(x) AS INTEGER) or a plain rounded number is fine; the exact rounding must match the reference, so round to a whole number).
If a department has no employees with a non-NULL salary, it has no average and must be excluded.
employee.salary may be NULL; NULLs are ignored when computing averages.
employee.department_id may be NULL; such employees belong to no department and are ignored.
The result may be empty if no department qualifies.

Example

employee		department
id	name	department_id	id	name
1	Alice	1	1	Engineering
2	Bob	1	2	Sales
3	Carol	2
4	Dave	2

Company average = (100 + 60 + 80 + 40) / 4 = 70. Engineering average = 80 > 70, Sales = 60 ≤ 70.

Expected result:

department	avg_salary
Engineering	80

Constraints

department.id is the primary key.
employee.department_id references department.id and is nullable.

Tips

Compute the company average in a scalar subquery and compare with HAVING.
The company average should be computed once; putting AVG over the whole table inside the HAVING is a common mistake that changes the answer.
Rounding: CAST(ROUND(AVG(salary)) AS INTEGER) behaves like PostgreSQL's ROUND(double precision) in SQLite.

Schema

CREATE TABLE department (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employee (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    salary        INTEGER,  -- NULL allowed
    department_id INTEGER REFERENCES department(id)  -- NULL allowed
);

Sample data

INSERT INTO department (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales'),
  (3, 'Marketing');

INSERT INTO employee (id, name, salary, department_id) VALUES
  (1, 'Alice',  100, 1),
  (2, 'Bob',    60,  1),
  (3, 'Carol',  80,  2),
  (4, 'Dave',   40,  2),
  (5, 'Eve',    70,  3);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: departments whose average salary exceeds the company average.
-- NULL salaries are ignored by AVG; departments with no non-NULL salaries drop out.
SELECT d.name AS department,
       CAST(ROUND(AVG(e.salary)) AS INTEGER) AS avg_salary
FROM department d
JOIN employee e ON e.department_id = d.id
GROUP BY d.id, d.name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employee)
ORDER BY d.name;

Key concepts

INNER JOIN
GROUP BY
HAVING
Subquery in HAVING
AVG
COALESCE