Above Their Department's Average

Mediumsql

Write a query returning id, name, salary for every employee whose salary is above the AVERAGE salary of their own department, ordered by id.

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

Sample data:

sql
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Asha', 'Engineering', 90000),
(2, 'Rohan', 'Engineering', 60000),
(3, 'Neha', 'Sales', 70000),
(4, 'Vikram', 'Sales', 50000),
(5, 'Priya', 'Engineering', 75000);

Example 1

Input

(none)

Output

id	name	salary
1	Asha	90000
3	Neha	70000

A correlated subquery references the outer row's department, re-evaluating AVG per employee. Reference: SELECT id, name, salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) ORDER BY id;

Above Their Department's Average

Related Problems

Input (stdin)

Output