Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table of employees with their salaries and department names, calculate the average salary for each department and display the top 5 departments with the highest average salary. Sample Tables (CREATE + INSERT):

CREATE TABLE employees (
 id INT,
 name VARCHAR(50),
 salary INT,
 department VARCHAR(50)
);
INSERT INTO employees (id, name, salary, department)
VALUES (1, 'John', 50000, 'Engineering'),
 (2, 'Jane', 60000, 'Engineering'),
 (3, 'Bob', 70000, 'Sales'),
 (4, 'Sara', 80000, 'Sales'),
 (5, 'Mike', 90000, 'Marketing'),
 (6, 'Lisa', 100000, 'Marketing');

Query:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 5;

Explanation:

This query uses the AVG() function to calculate the average salary for each department and then uses the ORDER BY and LIMIT keywords to select the top 5 departments with the highest average salary. This can be helpful for companies to identify which departments may need salary adjustments to stay competitive in the market.