Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Example: employees (id, name, dept, salary). **ROW_NUMBER**: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees. **RANK**: Same but RANK()—ties get same rank, gap after. **DENSE_RANK**: Same rank, no gap. Output: dept, name, salary, rn, rank,...
Example: employees (id, name, dept, salary). ROW_NUMBER: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees. RANK: Same but RANK()—ties get same rank, gap after. DENSE_RANK: Same rank, no gap. Output: dept, name, salary, rn, rank, dense_rank. Why PARTITION BY: Defines groups; ORDER BY defines order within group. Use ROW_NUMBER for unique ordering; RANK for Olympics-style; DENSE_RANK for top-N without gaps. Scalability: Window functions can cause spills if partition is huge; ensure ORDER BY columns are indexed when supported.
Red Flag: PARTITION BY without ORDER BY when semantics require order. Pro-Move: 'PARTITION BY dept ORDER BY salary DESC NULLS LAST—handles nulls.'
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.