Average Salary by DepartmentCompany: Google | Difficulty: medium

Calculate the average salary for each department.

Return the columns in this exact order: department and avg_salary. Ignore employees whose salary is NULL. Round the average salary to two decimal places.

Sort by average salary in descending order. If two departments have the same average, sort by department name in descending alphabetical order.

Table Schema

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

Example Input: `employees`

employee_id	name	department	salary
1	Alice Smith	Engineering	95000
2	Bob Jones	Engineering	80000
3	Diana Prince	Sales	65000
4	Evan Wright	Sales	70000
5	Fiona Green	Marketing	68000

Example Output Explanation

Engineering has an average salary of 87500. Sales has an average of 67500, and Marketing has an average of 68000. The results are ordered from the highest average salary to the lowest.

SchemaDROP TABLE IF EXISTS employees; CREATE TABLE employees ( employee_id INT, name VARCHAR(100), department VARCHAR(80), salary INT ); INSERT INTO employees VALUES (1,'Alice Smith','Engineering',95000), (2,'Bob Jones','Engineering',80000), (3,'Diana Prince','Sales',65000), (4,'Evan Wright','Sales',70000), (5,'Fiona Green','Marketing',68000);← All SQL Challenges · Google Questions · Learn SQL Free