𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the department with the highest average salary.

𝗠𝗲: Challenge accepted! 💪

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

💡 Explanation:
The query calculates the average salary for each department and returns the department with the highest average salary.

• GROUP BY department groups employees by department
• AVG(salary) calculates the average salary for each department
• ORDER BY average_salary DESC sorts departments from highest to lowest average salary
• LIMIT 1 returns only the top department

This question tests your understanding of:
✅ GROUP BY
✅ Aggregate Functions AVG
✅ ORDER BY
✅ LIMIT

🎯 Expected Output
Department Average_Salary
IT 88,500

🚀 Bonus Handles Ties
If multiple departments share the highest average salary, use DENSE_RANK():

SELECT
department,
average_salary
FROM (
SELECT
department,
AVG(salary) AS average_salary,
DENSE_RANK() OVER (
ORDER BY AVG(salary) DESC
) AS rnk
FROM employees
GROUP BY department
) ranked
WHERE rnk = 1;

This version returns all departments tied for the highest average salary.

🚀 Whenever you see questions like highest, lowest, top N, or rank, think beyond LIMIT. Ask yourself: What if there's a tie? Window functions like DENSE_RANK() often provide a more complete solution.

❤️ React with ❤️ for more SQL interview challenges!