Departments with a payroll above average
Hard · Company database · WITH (CTE), GROUP BY, COUNT, SUM, AVG
A department's payroll is the total of its employees' salaries. Find the departments whose payroll is above the average payroll of the departments that have employees (a department with no employees is not part of that average).
Return the department name and its payroll, in that order, highest payroll first.
Expected result
| name | total |
|---|---|
| Engineering | 1137000 |
| Sales | 462000 |
The company tables
assignments: 30 rows, first 5 shown
| id | employee_id | project_id | hours |
|---|---|---|---|
| 1 | 8 | 1 | 320 |
| 2 | 9 | 1 | 410 |
| 3 | 11 | 1 | 380 |
| 4 | 12 | 2 | 290 |
| 5 | 10 | 2 | 150 |
departments: 8 rows, first 5 shown
| id | name | location |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | Sales | London |
| 3 | Marketing | London |
| 4 | Finance | Singapore |
| 5 | Human Resources | Bengaluru |
employees: 27 rows, first 5 shown
| id | name | department_id | manager_id | job_title | salary | hire_date |
|---|---|---|---|---|---|---|
| 1 | Meera Krishnan | NULL | NULL | Chief Executive Officer | 250000 | 2015-03-01 |
| 2 | Daniel Okoye | 1 | 1 | Chief Technology Officer | 250000 | 2015-03-01 |
| 3 | Sophie Laurent | 2 | 1 | Head of Sales | 150000 | 2017-01-09 |
| 4 | Rahul Bansal | 4 | 1 | Finance Director | 166000 | 2016-11-21 |
| 5 | Hannah Schmidt | 5 | 1 | Human Resources Manager | 110000 | 2018-04-02 |
projects: 10 rows, first 5 shown
| id | name | department_id | budget | start_date | end_date |
|---|---|---|---|---|---|
| 1 | Payments Platform | 1 | 450000 | 2025-01-06 | 2025-09-30 |
| 2 | Mobile App Redesign | 1 | 300000 | 2025-03-03 | NULL |
| 3 | Data Warehouse | 1 | 380000 | 2025-06-02 | 2026-03-31 |
| 4 | Spring Campaign | 3 | 90000 | 2025-02-10 | 2025-05-30 |
| 5 | Partner Portal | 2 | 150000 | 2025-04-14 | 2025-12-19 |
-- Write your SQL here