MediumSQL
Employees Earning Above Their Department Average
SQLDatabases
Write a SQL query using a correlated subquery to find employees whose
salary is strictly above the average salary of their own department.
Return name, department, salary, ordered by department then salary
descending.
Table: `employees`
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | Engineering | 95000 |
| 2 | Bob | Engineering | 72000 |
| 3 | Carol | Engineering | 85000 |
| 4 | Dave | Marketing | 55000 |
| 5 | Eve | Marketing | 62000 |
| 6 | Frank | Marketing | 48000 |
Engineering avg = 84 000 → Alice (95k ✓), Carol (85k ✓), Bob (72k ✗)
Marketing avg = 55 000 → Eve (62k ✓), Dave (55k = avg ✗), Frank (48k ✗)
Expected output
Alice|Engineering|95000
Carol|Engineering|85000
Eve|Marketing|62000Sample tests
Test #1Engineering avg=84000, Marketing avg=55000
Input: "CREATE TABLE employees (id INTEGER, name TEXT, department TEXT, salary INTEGER);\nINSERT INTO employees VALUES\n (1, 'Alice', 'Engineering', 95000),\n (2, 'Bob', 'Engineering', 72000),\n (3, 'Carol', 'Engineering', 85000),\n (4, 'Dave', 'Marketing', 55000),\n (5, 'Eve', 'Marketing', 62000),\n (6, 'Frank', 'Marketing', 48000);"
Output: "Alice|Engineering|95000\nCarol|Engineering|85000\nEve|Marketing|62000"