Find Top Two Employees Per Department

Question

Find the top 2 employees in each department using a window function.

Accepted Answer

```sql
SELECT department_id, name, salary
FROM (
    SELECT department_id, name, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_position
    FROM employees
) as ranked_employees
WHERE rank_position <= 2;
```

This query uses the `DENSE_RANK()` window function to assign a rank to each employee within their respective department based on their salary in descending order, then filters the result set to include only those with a rank of 2 or less.