codejump
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`

idnamedepartmentsalary
1AliceEngineering95000
2BobEngineering72000
3CarolEngineering85000
4DaveMarketing55000
5EveMarketing62000
6FrankMarketing48000

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|62000

Sample 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"
← All challenges