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

You have 2 minutes to solve this SQL query.

Find the top 3 highest-paid employees 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 salary_rank

    FROM employees

) ranked

WHERE salary_rank <= 3

ORDER BY department, salary DESC;

💡 Explanation:

The query uses the DENSE_RANK() window function to rank employees based on salary within each department.

• PARTITION BY department creates a separate ranking for every department.

• ORDER BY salary DESC ranks the highest salary first.

• DENSE_RANK() assigns the same rank to employees with identical salaries.

• The outer query returns only employees with a rank of 3 or less.

This question tests your understanding of:

✅ Window Functions

✅ DENSE_RANK()

✅ Top N per Group

✅ Partitioning Data

🎯 Expected Output Example

Employee  Department Salary Rank

John  IT  95,000 1

Alice  IT  95,000 1

Bob  IT  90,000 2

Mike  IT  85,000 3

Sarah  HR  80,000 1

🚀 Know when to use each ranking function:

• ROW_NUMBER() → No ties (unique ranking)

• RANK() → Leaves gaps after ties

• DENSE_RANK() → No gaps after ties (ideal for Top N with ties)

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