Find Second Highest Salary Per Department

Question

Find the second-highest-paid employee in each department.

Accepted Answer

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

This query uses the `DENSE_RANK()` window function to assign a rank to each employee's salary within their respective department, then filters for the records where the rank is 2.