𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:

You have 2 minutes to solve this SQL query.

Find the employees who have the highest salary in each department.

𝗠𝗲: Challenge accepted! 💪

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;

💡 Explanation:

This query uses the DENSE_RANK() window function to rank employees by salary within each department.

• PARTITION BY department creates separate rankings for each department.

• ORDER BY salary DESC ranks the highest salary as 1.

• DENSE_RANK() ensures that if multiple employees have the same highest salary, they all receive Rank 1.

• The outer query filters only the employees with rnk = 1.

This question tests your knowledge of:

✅ Window Functions

✅ DENSE_RANK() vs RANK() vs ROW_NUMBER()

✅ Partitioning Data

🎯 Output Example

Employee | Department | Salary

John | IT | 95,000

Sarah | HR | 80,000

David | Finance | 90,000

Alice | IT | 95,000

(John and Alice both appear because they share the highest salary in the IT department.)

🚀 Whenever an interview question asks for the top N records per group, think of window functions.

DENSE_RANK(), RANK(), and ROW_NUMBER() are among the most commonly tested SQL concepts.

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