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

Find the third highest salary from the employees table without using LIMIT or TOP.

𝗠𝗲: Challenge accepted! 💪

SELECT
salary
FROM (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 3;

💡 Explanation:
This query uses the DENSE_RANK() window function to rank distinct salary values in descending order.

• ORDER BY salary DESC assigns Rank 1 to the highest salary.
• DENSE_RANK() ensures duplicate salaries receive the same rank.
• The outer query filters for salary_rank = 3, returning the third highest distinct salary.

🎯 Expected Output Example
Salary Rank
95,000 1
90,000 2
85,000 3

Output:
Third Highest Salary
85,000

🚀 Alternative Solution Using a Correlated Subquery
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary
);

This solution avoids window functions and is commonly asked to test your understanding of correlated subqueries.

🚀 Questions involving the Nth highest or Nth lowest value are interview favorites. Be comfortable solving them using:

1. DENSE_RANK()
2. RANK()
3. Correlated Subqueries
4. Common Table Expressions (CTEs)

Being able to provide multiple approaches leaves a strong impression on interviewers.

❤️ React with ❤️ for more interview challenges!