Reviewed by Aditya Kumar · Last reviewed 2026-03-24
SELECT department_id, employee_id, salary, MAX(salary) OVER (PARTITION BY department_id) AS max_salary FROM employees. Or with RANK: SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) r FROM employees) WHERE r = 1. **Why window**: Avoid...
SELECT department_id, employee_id, salary, MAX(salary) OVER (PARTITION BY department_id) AS max_salary FROM employees. Or with RANK: SELECT FROM (SELECT , RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) r FROM employees) WHERE r = 1. Why window: Avoid correlated subquery; single pass. Trade-off: MAX returns all rows with max; RANK returns only top row(s).
Red Flag: Subquery per department when window avoids it. Pro-Move: 'MAX over partition—one scan; RANK when we need the person, not just the value.'
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.