intermediateTop 30 Scenario-Based Questions
Write a query to find the employee with the 3rd highest salary in each department.
SELECT * FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM employees) ranked WHERE dr = 3; Why DENSE_RANK: if multiple employees share the same salary rank, DENSE_RANK assigns same rank without skipping. So the '3rd distinct salary' is what we get. Alternative using ROW_NUMBER if you want the 3rd person (not 3rd salary): replace DENSE_RANK with ROW_NUMBER. Al
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response