Department Compensation ReviewCompany: Google | Difficulty: hard

Google's people analytics team wants to identify departments whose average employee salary is strictly above 75000.

Return the department name, average salary rounded to two decimal places, and total number of employees in each qualifying department. Employees with missing salary values should still be included in the employee count, but their missing salary must not affect the average.

Sort by average salary from highest to lowest. If two departments have the same average salary, sort their names in descending alphabetical order.

Table Schema

Table	Column	Type
`employees`	`employee_id`	integer
`employees`	`employee_name`	varchar(100)
`employees`	`department`	varchar(80)
`employees`	`salary`	integer

Example Input: `employees`

employee_id	employee_name	department	salary
1	Priya Raman	Search	92000
2	Marcus Chen	Search	88000
3	Diego Ortiz	Ads	71000
4	Lena Kowalski	Ads	66000
5	Farah Haddad	Cloud	69500

Example Output Explanation

Search has an average salary of 90000.00 (92000 + 88000, divided by 2), which exceeds the 75000 threshold, so it is returned with an employee count of 2. Ads and Cloud have average salaries below the threshold and are excluded.

SchemaDROP TABLE IF EXISTS employees; CREATE TABLE employees ( employee_id INT, employee_name VARCHAR(100), department VARCHAR(80), salary INT ); INSERT INTO employees VALUES (1, 'Priya Raman', 'Search', 92000), (2, 'Marcus Chen', 'Search', 88000), (3, 'Diego Ortiz', 'Ads', 71000), (4, 'Lena Kowalski', 'Ads', 66000), (5, 'Farah Haddad', 'Cloud', 69500);← All SQL Challenges · Google Questions · Learn SQL Free