Find Employees With Salary Rank Two

Question

Find employees with salary rank 2 in each department.

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 salary_rank
    FROM employees
) as ranked_employees
WHERE salary_rank = 2;
```

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