Top-N per group with window functions

Question

Accepted Answer

The inner query assigns row numbers partitioned by dept, ordered by salary DESC. Within Eng: Alice=1, Bob=2. Within HR: Dave=1, Carol=2. The outer WHERE rn = 1 keeps only the top earner per department - Alice in Eng, Dave in HR. This 'top-N per group' pattern is one of the most common interview questions involving window functions.