Find Highest and Lowest Employee Salaries

Question

Find the highest-paid employee and lowest-paid employee in each department using window functions.

Accepted Answer

```sql
WITH SalaryStats AS (
    SELECT 
        department_id, 
        name, 
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_desc,
        RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) as rank_asc
    FROM employees
)
SELECT 
    department_id, 
    name, 
    salary,
    CASE 
        WHEN rank_desc = 1 THEN 'Highest'
        WHEN rank_asc = 1 THEN 'Lowest'
    END as salary_type
FROM SalaryStats
WHERE rank_desc = 1 OR rank_asc = 1;
```

This query uses Common Table Expressions (CTE) and window functions to rank employees by salary within each department, then filters for the top and bottom records.