Departments above the average department total
A department's salary total is the sum of its employees' salaries. Return the dept_id and salary total of every department whose total is above the average of all department totals.
Leave employees with no department out of both the totals and the average.
Rows can come back in any order. Column names do not matter; the values do.
Sample database: Company: departments, employees and orders
Three tables. departments (id, name, city). employees (id, name, email, dept_id, manager_id, salary, bonus, hire_date): dept_id and manager_id may be NULL, bonus may be NULL, hire_date is text in the form YYYY-MM-DD. orders (id, employee_id, customer, amount, status, order_date): employee_id is the sales rep (an employee id), amount may be NULL, status is DELIVERED, SHIPPED, PENDING or CANCELLED in capitals, and order_date is text in the form YYYY-MM-DD. Foreign keys are not enforced, so an order may point to an employee who does not exist.
| id | name | city |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | QA | Pune |
| 3 | Sales | Mumbai |
| 4 | HR | Pune |
| 5 | Support | Chennai |
| id | name | dept_id | manager_id | salary | bonus | hire_date | |
|---|---|---|---|---|---|---|---|
| 1 | Asha Rao | asha@shop.in | 1 | NULL | 150000 | 20000 | 2018-03-01 |
| 2 | Vikram Shah | vikram@shop.in | 1 | 1 | 95000 | NULL | 2020-07-15 |
| 3 | Neha Gupta | neha@shop.in | 2 | 1 | 82000 | 5000 | 2021-01-10 |
| 4 | Rohan Mehta | rohan@shop.in | 2 | 3 | 64000 | NULL | 2022-06-01 |
| 5 | Priya Nair | priya@shop.in | 2 | 3 | 64000 | 3000 | 2023-02-20 |
| 6 | Arjun Das | arjun@shop.in | 3 | 1 | 70000 | 15000 | 2019-11-05 |
| 7 | Kavya Iyer | kavya@shop.in | 3 | 6 | 52000 | NULL | 2024-01-08 |
| 8 | Imran Khan | imran@shop.in | 4 | 1 | 58000 | 2000 | 2020-09-30 |
| 9 | Sneha Patil | neha@shop.in | NULL | 1 | 47000 | NULL | 2025-04-14 |
| 10 | Dev Malhotra | dev@shop.in | 1 | 2 | 95000 | 8000 | 2022-12-01 |
| id | employee_id | customer | amount | status | order_date |
|---|---|---|---|---|---|
| 101 | 6 | Acme Ltd | 12000 | DELIVERED | 2026-01-05 |
| 102 | 6 | Bharat Traders | 4500 | CANCELLED | 2026-01-09 |
| 103 | 7 | Acme Ltd | 8000 | DELIVERED | 2026-02-11 |
| 104 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 105 | 6 | Delta Foods | 3000 | PENDING | 2026-03-02 |
| 106 | 7 | Bharat Traders | 9500 | DELIVERED | 2026-03-15 |
| 107 | 12 | Echo Stores | 7000 | DELIVERED | 2026-03-18 |
| 108 | 6 | Acme Ltd | NULL | PENDING | 2026-04-01 |
| 109 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 110 | 8 | Fresh Mart | 2500 | DELIVERED | 2026-04-10 |
Run shows your rows. Submit compares them with the expected rows.