Reviewed by Aditya Kumar · Last reviewed 2026-03-24
SELECT * FROM (SELECT employee_id, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk FROM employees) t WHERE rk = 2. **Per-department**: PARTITION BY department. **Ties**: Multiple employees can share 2nd....
SELECT * FROM (SELECT employee_id, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk FROM employees) t WHERE rk = 2. Per-department: PARTITION BY department. Ties: Multiple employees can share 2nd. Single-dept: Only one 2nd per dept; dept with <2 employees returns no row for that dept.
Red Flag: Using RANK—two 1st places would make next 3rd, not 2nd. Pro-Move: 'We used DENSE_RANK and added dept size check: only depts with 3+ employees to ensure meaningful 2nd.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.