JavaScript is required for SkillDojo to work properly. Please enable JavaScript and refresh the page.

Department Managers with Above-Average Salaries | Algorithm

Hard

HardjoinsSQL

Department Managers with Above-Average Salaries

HR is preparing a report to identify managers whose salaries are above the average salary of their respective departments. For each such manager, list their name, department name, salary, and the department's average salary. Only include managers who have at least one direct report. Order the results by department name and then by manager name alphabetically.

Return

name : the manager's name department_name : the name of the manager's department salary : the manager's salary department_avg_salary : the average salary in the manager's department Only include managers whose salary is strictly greater than their department's average and who have at least one direct report. Order by department_name, then name .

Expected output

name	department_name	salary	department_avg_salary
3 rows — values hidden, that's the puzzle

Your query should return 3 rows with these columns. Your column aliases don't need to match.

Tables · HR — Departments & Employees

departments

idINTEGERpknameTEXT

employees

idINTEGERpknameTEXTdepartment_idINTEGER→departments.idnullmanager_idINTEGER→employees.idnullsalaryINTEGERhired_onTEXT

id	name	department_id	manager_id	salary	hired_on
1	Ana Reyes	1	NULL	150000	2018-03-01
2	Ben Cho	1	1	120000	2019-06-15
3	Cara Diaz	1	1	120000	2020-01-20
4	Dan Ellis	2	NULL	140000	2017-11-05
5	Eve Fox	2	4	95000	2021-02-10

0 of 4 hints revealed

Submit an attempt first.